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?

CodePudding user response：

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

CodePudding user response：

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