Pull latest record in table with duplicate ID's

Question

ID	DATE	col1
1	01-01-2022	apple
1	01-02-2022	orange

It's been a while since I've worked with sql (using oracle fyi). If I want to pull unique ID's with the latest Date (in this case only the second row should be pulled in the result), how can I do that?

I tried:

SELECT ID, MAX(DATE), col1
FROM table
GROUP BY ID

Now this doesn't work because I need to aggregate col1 in the SELECT or throw it in the GROUP BY. If I throw it in the GROUP BY, I'll get both rows in the result, right? But I also don't see the point in aggregating col1 if I want the row based on max(date). Am I missing something here?

Does this answer your question? [Oracle SQL query: Retrieve latest values per group based on time](https://stackoverflow.com/questions/2000908/oracle-sql-query-retrieve-latest-values-per-group-based-on-time) — astentx, Jan 20 '22 at 17:10

Md. Tarikul Islam Soikot · Accepted Answer · 2022-01-20T18:53:10.410

You can use LAG(), LEAD() ROW_NUMBER() function to achieve this goal. Check my below queries.

Using LAG():

SELECT 
    ID, 
    DATE, 
    col1
FROM 
(
    SELECT 
        *,
        LAG(DATE, 1) OVER(Partition By ID ORDER BY DATE DESC) AS DateOfPreviousRow
     FROM Table
) T
WHERE DateOfPreviousRow IS NULL

Using LEAD():

SELECT 
    ID, 
    DATE, 
    col1
FROM 
(
    SELECT 
        *,
        LEAD(DATE, 1) OVER(Partition By ID ORDER BY DATE) AS DateOfPreviousRow
    FROM Table
) T
WHERE DateOfPreviousRow IS NULL

Using ROW_NUMBER():

SELECT T.* FROM 
(
   SELECT 
       *,
       ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE DESC) AS ROWNumber
   FROM Table
) T
WHERE ROWNumber = 1

score 0 · Answer 2 · answered Jan 20 '22 at 17:06

0

You can use ROW_NUMBER(). For example:

select *
from (
  select t.*,
    row_number() over(partition by id order by date desc) as rn
  from t
) x
where rn = 1

answered Jan 20 '22 at 17:06

The Impaler

45,731
9
39
76

Pull latest record in table with duplicate ID's

2 Answers2