Return Only Top Row of SQL Server from duplicated data but different id

Question

I have the below table:

ID|NAME|CREATED         |TYPE|CARD_NO|
======================================
1 |JOHN|2022-09-21 09:00| 1  |1111111|
2 |JOHN|2022-09-21 09:05| 2  |1111111|
3 |DOE |2022-09-21 09:00| 1  |2222222|
4 |DOE |2022-09-21 09:05| 2  |2222222|
5 |DOE |2022-09-21 09:10| 3  |2222222|

I want to return only the most recent datatime of each row like below:

ID|NAME|CREATED         |TYPE|CARD_NO|
======================================
2 |JOHN|2022-09-21 09:05| 2  |1111111|
5 |DOE |2022-09-21 09:10| 3  |2222222|

My query is:

SELECT ID, NAME,MAX(CREATED),TYPE,CARD_NO FROM users group by ID,NAME,TYPE,CARD_NO

However the result is not what I expected, please help me. thank you.

Answer 1

Use ROW_NUMBER coupled with a common table expression or derived table to work out the latest row

;with GetLatestRow
AS(
   SELECT RN=row_number()over(partition by t.[name] order by t.Created DESC),    --desc to put the lastest row on top
          t.*
   FROM  users t
)
SELECT ID, NAME, CREATED,TYPE,CARD_NO
FROM GetLatestRow
WHERE RN=1