CODEWITHSUNDEEP
sql-server
NullPointer
NullPointer

Reputation: 2184

Filter out all but one duplicate row in SQL based on a matching key

I'm trying to filter out duplicate entries (create a view) based on a key in a table. Consider the table below

enter image description here

I want to filter out all but one duplicate records based on EmployeeID. It doesn't matter which record is retained, although it would be nice to have a mechanism to specify a whereClause on it too. The target view looks like this

enter image description here

I tried using a query based on partition by however I can't quite get my result right.

Is this possible?

Thanks in advance

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

The simplest method uses row_number():

select t.*
from (select t.*,
             row_number() over (partition by employeeid order by employeeid) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions