CODEWITHSUNDEEP
sql-server-2012
user2536008
user2536008

Reputation: 215

SQL Server: Eliminate duplicates based on two columns

I have the following table in SQL Server 2012:

enter image description here

I need to select distinct rows and only whose contact equal to "Own". The result should like this:

enter image description here

I tried the following query:

with cte as
(
    select 
        row_number() over (partition by contact order by SiteNum) rn,
        SiteNum, SiteAdd, Description, Contact
    from 
        Name
)
select * 
from cte 
where rn = 1

I am not sure if it can be accomplished using different approach like temp table or where clause.

Upvotes: 1

Views: 58

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

I think you need to partition by SiteNum and order by the Contact, which is the opposite of what you were doing. But other than this, your approach seems on the right track. Try this query:

with cte as
(
    select row_number() over (partition by SiteNum
        order by case when Contact = 'Own' then 0 else 1 end) rn,
        SiteNum, SiteAdd, Description, Contact
    from Name
)
select * from cte 
where rn = 1

Note that I used a CASE expression for the ORDER BY clause which explicitly checks for a contact called 'Own'. We could have tried using MIN() or MAX() and relied on non matching records having NULL, but this could lead to problems later on should your table have other contact values besides 'Own'.

Upvotes: 1

Ranadip Dutta
Ranadip Dutta

Reputation: 9133

This where clause should give you what you are expecting

Select DISTINCT SiteAdd from table where Contact = 'Own'

You can put the distinct on the entire row also, in case there are duplicate rows. 

Select DISTINCT * from table where Contact = 'Own'

Your original query will work based on the logic. Just change the TYPO :

wehre rn = 1 to where rn=1

Hope it helps.

Upvotes: 0

Related Questions