CODEWITHSUNDEEP
sql-servert-sql
SoupOfStars
SoupOfStars

Reputation: 147

T-sql Narrow down results where duplicates exist

I am trying to find a solution where i can limit my results on a table of data where duplicated ids are found.

Example sample of data:

ID   | Typeofstaff | Role
---------------------------------------  
779  |    2        |  284
779  |    5        |  276
1299 |    5        |  488
1299 |    2        |  322
627  |    4        |  456
627  |    4        |  476
898  |    5        |  321
999  |    3        |  567
1027 |   null      |  890
1027 |    2        |  891

If the [ID] is duplicated and they have a [Typeofstaff] = 2 then just show me the row with the [Typeofstaff] = 2 else show me the next highest number either 3, 4 or 5 in the [Typeofstaff] but not a duplicate [ID].

If the [ID] & [Typeofstaff] is duplicated then show me either one of the rows.

If no duplicates exist in the [ID] then display the row as normal.

Upvotes: 0

Views: 82

Answers (1)

Hogan
Hogan

Reputation: 70513

You can use ROW_NUMBER() to do this:

SELECT ID, Typeofstaff, Role,
FROM (
  SELECT ID, Typeofstaff, Role,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY COALESCE(TypeofStaff,1000) ASC) AS RN
) X
WHERE RN = 1

Upvotes: 1

Related Questions