Home » SQL & PL/SQL » SQL & PL/SQL » Count of records with the max(date) and Max(lastrecordid) (SQL)
Count of records with the max(date) and Max(lastrecordid) [message #682723] Thu, 12 November 2020 01:29 Go to next message
enniwesw
Messages: 2
Registered: November 2020
Junior Member
I need assistance with a query that gives me a count of all records having the largest LASTRECORDID and MAX(Load_date)

select ID, DEVICEID, REGION, MAX(LOAD_DATE) as LOAD_DATE, CURRENT_TIMESTAMP AS TestDate, COUNT(1) AS TheCount, MAX(LASTRECORDID) AS LASTRECORDID
from TEP_EM.GER_DATARECORDS
GROUP BY ID, DEVICEID, REGION ,LOAD_DATE, LASTRECORDID

i am getting

ID DEVICEID REGION LOAD_DATE TESTDATE THECOUNT LASTRECORDID
1239 1603 NGA 2020-11-10 07:12:35.550 2020-11-12 09:02:20.370 1 323066578
1239 1603 NGA 2020-11-11 01:12:43.312 2020-11-12 09:02:20.370 1 323128118

i want to return only the below as it has the latest LOAD_DATE and largest LASTRECORDID

1239 1603 NGA 2020-11-11 01:12:43.312 2020-11-12 09:02:20.370 1 323128118
Re: Count of records with the max(date) and Max(lastrecordid) [message #682724 is a reply to message #682723] Thu, 12 November 2020 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
  • The main question is "what is your grouping/partitioning criteria?".
Assuming it is "ID, DEVICEID".
  • Another one is "what do you want to count?" Is it the whole count for each "ID, DEVICEID" or the count for each "ID, DEVICEID" with the "max(load_date), max(lastrecordid)"?
Assuming it is the former.
  • "having the largest LASTRECORDID and MAX(Load_date)"; if the row with "max(load_date)" is not the same one than the row with "max(lastrecordid)", what is the first criteria "max(load_date)" or "max(lastrecordid)"?
Assuming here it is in the order you posted.

You can do something like (not tested as I have no table and no data):
with 
  data as (
    select ID, DEVICEID, REGION, LOAD_DATE, CURRENT_TIMESTAMP AS TestDate, 
           COUNT(*) over (partition by ID, DEVICEID) AS TheCount, LASTRECORDID,
           row_number() over (partition by ID, DEVICEID order by LASTRECORDID desc, Load_date desc) rn
    from GER_DATARECORDS
  )
select ID, DEVICEID, REGION, LOAD_DATE, TestDate, TheCount, LASTRECORDID
from data
where rn = 1
/

[Updated on: Thu, 12 November 2020 07:14]

Report message to a moderator

Re: Count of records with the max(date) and Max(lastrecordid) [message #682762 is a reply to message #682724] Tue, 17 November 2020 02:46 Go to previous message
enniwesw
Messages: 2
Registered: November 2020
Junior Member
Many thanks i am new to scripting and this forumn....

Oracle version Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

I wish to partition by ID, DEVICEID, REGION with my rank order by criteria LOAD_DATE

my count should be by ID, DEVICEID by region having the latest date. I managed to come up with this.

select REGION, LOAD_DATE, SYSTIMESTAMP as Test_Date, COUNT(1) AS TheCount 
from (select REGION, load_date, ROW_NUMBER() over(partition by ID, DEVICEID, REGION order by LOAD_DATE desc) rnk 
from TEP_EM.GER_DATARECORDS) d where rnk = 1 GROUP BY REGION ,LOAD_DATE;

CREATE TABLE
  GER_DATARECORDS
  (
    ID INTEGER, 
	DEVICEID INTEGER,
    LASTRECORDID INTEGER,
    TIMES TIMESTAMP(6),
    REGION VARCHAR2(6),
    LOAD_DATE TIMESTAMP(6)
  );
sample data attached


--moderator edit: [code] tags added, please do this yourself in future.

[Updated on: Wed, 18 November 2020 03:50] by Moderator

Report message to a moderator

Previous Topic: Date to Jewish/Hebrew Date (2 merged)
Next Topic: UPDATE query with three table join (3 merged)
Goto Forum:
  


Current Time: Sun May 16 14:02:41 CDT 2021