CODEWITHSUNDEEP
sqlsql-serverduplicates
Fler
Fler

Reputation: 392

How to remove duplicates via a select if there are two deciding columns

I have a table which looks similar to the below.

Table Example

I'm trying to select data where the row isn't a duplicate. For example, Row ID 1 and 2 are duplicates of each other as the PersonID and Location are the same. In this case, I'd only like to select one of these rows back. However, with rows 4 and 5, I'd like both of these returning as the location is different. How would I go about this?

Upvotes: 1

Views: 397

Answers (2)

Eli
Eli

Reputation: 2608

You can use a CTE for this as well.
The CTE will allow for you to add another column ranking your records; once you have that ranking you can filter them all out, delete them or do whatever else you need.

I've answered a similar question with regards to deleting duplicates in a similar scenario; see the answer here: Remove duplicate records except the first record in SQL

In your case, the query would be something along the lines of: 

;WITH NoDuplicates as (
    SELECT 
         PersonID
        ,[Location]
        ,ROW_NUMBER() OVER(PARTITION BY PersonID ,[Location] ORDER BY PersonID) ranked
    FROM #myTable
)
DELETE
FROM NoDuplicates
WHERE ranked = 1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use aggregation:

select min(id) as id, personid, location
from t
group by personid, location;

Upvotes: 3

Related Questions