Returning 1 row per group of the latest date

Hey I’ve got this table:

ID      Name       E_Date              RoomCode       RoomName     BedN 
------------------------------------------------------------------------
324      AB     2022-06-12 11:00          5              5         NULL
324      AB     2022-06-12 10:00          4              AGA       3
324      AB     2022-06-12 09:00          4              AGA       5
767      DE     2022-06-12 11:40          4              AGA       3
767      DE     2022-06-12 12:40          4              AGA       4

What I want is to get 1 row per ID of the most recent BedN:

ID      Name       E_Date              RoomCode       RoomName     BedN 
------------------------------------------------------------------------
324      AB     2022-06-12 11:00          5              5         NULL
767      DE     2022-06-12 12:40          4              AGA       4

Thank you for your help!

>Solution :

Use ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY E_Date DESC) rn
    FROM yourTable
)

SELECT ID, Name, E_Date, RoomCode, RoomName, BedN
FROM cte
WHERE rn = 1;

Or, use the following abbreviated version, which however might perform worse than the above:

SELECT TOP 1 WITH TIES ID, Name, E_Date, RoomCode, RoomName, BedN
FROM yourTable
ORDER BY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY E_Date DESC);