T-sql Narrow down results where duplicates exist

Question

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.

@squillman - TSQL is used by more platforms than just SQL Server (Sybase). However, some people will select the TSQL tag when they mean mysql or other platforms because... well... reasons. — Hogan
– Hogan, Commented Jan 10, 2017 at 20:57
@hogan Ah, I sit corrected! Didn't realize that about Sybase. And good point about others. — squillman
– squillman, Commented Jan 10, 2017 at 20:59

Hogan · Accepted Answer · 2017-01-10 20:54:28Z

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

Collectives™ on Stack Overflow

T-sql Narrow down results where duplicates exist

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related