Jeff Martinez Jeff Martinez - 2 months ago 6
SQL Question

Retrieve unique rows based on multiple parameters

This may be quite simple but I seem to lacking the proper way to phrase this question via search engines.

For a given TicketId there are unique identifiers based on LocId AND BinId. There can also be any number of TransIds for any given LocId + BinId combination.

What I need is to return only the records with the highest TransId.

TicketId | LocId | BinId | TransId
-------- | ----- | ----- | -------
316 | 111 | 100 | 1000
316 | 112 | 101 | 1001
316 | 113 | 105 | 1000
316 | 104 | 210 | 1001
316 | 104 | 210 | 1000
316 | 17 | 35 | 1000
316 | 17 | 35 | 1001
316 | 27 | 99 | 1001


So my expected results would be:

TicketId | LocId | BinId | TransId
-------- | ----- | ----- | -------
316 | 111 | 100 | 1000
316 | 112 | 101 | 1001
316 | 113 | 105 | 1000
316 | 104 | 210 | 1001
316 | 17 | 35 | 1001
316 | 27 | 99 | 1001


Any help would be greatly appreciated.

Answer

You can do this with a windowed ROW_NUMBER() function with a PARTITION on the LocId and BinId, ordering by the TransId DESC, and taking just the first result:

;With Cte As
(
    Select  *,
            Row_Number() Over (Partition By LocId, BinId Order By TransId Desc) As RN
    From    YourTable
)
Select  TicketId, LocId, BinId, TransId
From    Cte
Where   RN = 1