CODEWITHSUNDEEP
sql
Ian
Ian

Reputation: 391

Select all data with no duplicate data

I have some data in database:

Name | Country | Status

Mary | USA | Pending

Jane| Japan | Pending

Jane | Korea | Pending

Adrian | China | Pending

Peter | Singapore | Pending

Jack | Malaysia | Pending

Adrian | China | Updated

Jane | Japan | Updated

May I know how to use the SELECT query to select all the data with no duplicate data? (If the duplicates data exist, select only the Status with Updated)

Upvotes: 0

Views: 145

Answers (2)

Edper
Edper

Reputation: 9322

Try:

SELECT Name, Country, MAX(Status) as Status FROM (
SELECT TOP 100 PERCENT *
FROM NameCountry
ORDER BY Name ASC, Country ASC, Status DESC
) G
GROUP BY G.Name, G.Country
ORDER BY G.Name, G.Country

Check my Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

From your comment, you seem to mean only data where the first two columns are duplicated. The easiest way, I think, is to use row_number(), which is available in most databases:

select name, country, status
from (select t.*,
             row_number() over (partition by name, country
                                order by (case when status = 'Pending' then 0 else 1 end)
                               ) as seqnum
      from t
     ) t
where seqnum = 1

Upvotes: 0

Related Questions