SQL - How to select latest available record for each date in a given range

1
votes

I have a table (DATA_RECORDS) in a database which contains multiple records for the same date, but at different times, running from 2015-2018. What I am trying to do is select all records within a given date range and then select the latest record available for each date. The current code I have in SQL is:

SELECT NAME, DATE_LOADED, R_ID 
    FROM DATA_RECORDS 
    WHERE ((DATE_LOADED>=to_date('01/12/2018 00:00:00', 'dd/mm/yyyy HH24:MI:SS'))
    AND (DATE_LOADED<=to_date('31/12/2018 23:59:59', 'dd/mm/yyyy HH24:MI:SS')))
    ORDER BY DATE_LOADED DESC;

Where the column names are 'NAME','DATE_LOADED' and 'R_ID'. The above gives the following results:

NAME      |DATE_LOADED         |R_ID
-------------------------------------
RECORD_1  |31/12/2018 17:36:38 |1234
RECORD_2  |31/12/2018 10:15:11 |1235
RECORD_3  |30/12/2018 16:45:23 |1236
RECORD_4  |30/12/2018 09:06:54 |1237
RECORD_5  |30/12/2018 07:53:30 |1238

etc... As you can see, there is also not a consistent number of uploads per day.

What I want is to select

NAME      |DATE_LOADED         |R_ID
-------------------------------------
RECORD_1  |31/12/2018 17:36:38 |1234
RECORD_3  |30/12/2018 16:45:23 |1236

I'm very new to SQL so any help would be appreciated.

N.B: I'm using Oracle SQL Developer and I only have read-only access to the database so I cannot create any new tables or modify the current table.

2
sqloracle

2 Answers

1
votes

I would write this logic as:

SELECT NAME, DATE_LOADED, R_ID 
FROM DATA_RECORDS 
WHERE DATE_LOADED >= DATE '2018-01-12' AND
      DATE_LODED < DATE '2018-12-31'
ORDER BY DATE_LOADED DESC;

Then a simple method is ROW_NUMBER() -- along with extracting only the date from the date/time value:

SELECT NAME, DATE_LOADED, R_ID
FROM (SELECT NAME, DATE_LOADED, R_ID ,
             ROW_NUMBER() OVER (PARTITION BY TRUNC(DATE_LOADED) ORDER BY DATE_LOADED DESC) as seqnum
      FROM DATA_RECORDS 
      WHERE DATE_LOADED >= DATE '2018-01-12' AND
            DATE_LODED < DATE '2018-12-31'
     ) dr
WHERE seqnum = 1
ORDER BY DATE_LOADED DESC;
0
votes

YOu can use correlated subquery

select * from tablename a where date in
   (select max(DATE_LOADED) from tablename b where cast(a.DATE_LOADED as date)=cast(b.DATE_LOADED as date)) and 
   ((DATE_LOADED>=to_date('01/12/2018 00:00:00', 'dd/mm/yyyy HH24:MI:SS'))
    AND (DATE_LOADED<=to_date('31/12/2018 23:59:59', 'dd/mm/yyyy HH24:MI:SS')))