Select ALL MAX dates for the same ID-CodePudding

I created the row number over partition by ID for a table. I would like to select MAX dates for each ID, but some MAX dates are the same and I would like to select them as well. How can I achieve this selection?

Original Table:

ROW_NUM	EMP_ID	EMP_ID2	ID	DATE
1	568	444	4220	11-25-2020
2	568	501	4220	11-25-2020
3	569	443	4220	11-01-2020
4	510	501	4220	11-12-2020
1	550	411	4221	12-25-2020
2	568	520	4221	12-25-2020
3	410	415	4221	12-25-2020
4	510	591	4221	11-12-2020
1	954	345	4225	09-25-2020
2	568	520	4225	09-25-2020
3	400	789	4225	09-25-2020
4	510	554	4225	09-25-2020
5	210	801	4225	05-12-2020

Table after selection:

ROW_NUM	EMP_ID	EMP_ID2	ID	DATE
1	568	444	4220	11-25-2020
2	568	501	4220	11-25-2020
1	550	411	4221	12-25-2020
2	568	520	4221	12-25-2020
3	410	415	4221	12-25-2020
1	954	345	4225	09-25-2020
2	568	520	4225	09-25-2020
3	400	789	4225	09-25-2020
4	510	554	4225	09-25-2020

Thank you.

CodePudding user response：

Use RANK instead of ROW_NUMBER:

WITH cte AS (
    SELECT *, RANK() OVER (PARTITION BY ID ORDER BY DATE DESC) rnk
    FROM yourTable
)

SELECT ROW_NUM, EMP_ID, EMP_ID2, ID, DATE
FROM cte
WHERE rnk = 1
ORDER BY ID;

CodePudding user response：

You can always join to a subquery:

SELECT t.*
FROM my_table t
LEFT JOIN (SELECT id, MAX(date) AS max_date FROM my_table GROUP BY 1) subq
    ON subq.id = t.id
WHERE t.date = subq.max_date
ORDER BY t.id;

CodePudding user response：

use rank instead:

select * from (
   select rank() over (partition by id order by date desc) rn
   from tablename
) t where rn = 1;