0

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.

3

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.

0

I know, it's an old question, but another solution would be FIRST_VALUE:

SELECT DISTINCT
       re.reservationID,
       FIRST_VALUE(re.id)         OVER (PARTITION BY re.reservationID ORDER BY re.swapNumber DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) id,
       FIRST_VALUE(re.newRoom)    OVER (PARTITION BY re.reservationID ORDER BY re.swapNumber DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) room,
       FIRST_VALUE(re.swapNumber) OVER (PARTITION BY re.reservationID ORDER BY re.swapNumber DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) highest_swapNumber
  FROM @RoomExchange AS re

You'll need the DISTINCT, because you'll get otherwise double records. The ROWS BETWEEN prevents it from creating a work table in the tempdb (what it would do with the default RANGE BETWEEN...). Of course there is also a LAST_VALUE() function, which could be used similar (beside the DESC sort order)), but as far I know, it uses always the tempdb and is slower for this reason.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.