SQL Server – Returning Only the Row with Maximum Value from Related Rows

greatest-n-per-groupsql serversql-server-2012t-sql

In the example below how do I get the rows where the swap id is the maximum value in relation to the reservationId? I am currently joining multiple tables to get customer, reservation, room etc information. But I don't want rows with a swapnumber of 1 if a value is higher then that. 

    DECLARE @RoomExchange TABLE (ID INT IDENTITY(1,1), reservationID int, 
                     swapNumber int, oldRoom int, newRoom int)

INSERT INTO @RoomExchange (reservationID, swapNumber, oldRoom, newRoom)
SELECT 12, 1, 1,2   UNION ALL
SELECT 13, 1, 3,4   UNION ALL
SELECT 14, 1, 5,6   UNION ALL
SELECT 14, 2, 6,5   UNION ALL
SELECT 15, 1, 7,8   UNION ALL
SELECT 15, 2, 8,9   UNION ALL
SELECT 15, 3, 9,7   UNION ALL
SELECT 16, 1, 10,11

So above i only want rows where the ID would be

1, 2, 4, 7 and 8.

Best Answer

You can use window functions:

select id, reservationID swapNumber, oldRoom, newRoom
from (
    select id, reservationID swapNumber, oldRoom, newRoom
         , row_number() over (partition by reservationId
                              order by swapNumber desc) as rn
    from @RoomExchange
) as T
where rn = 1;

By reversing the sort order we can pick the first one for each partition.

Related Question