In SQL, how do I select unique user id when it may have two different observations?

user_8888112 0
Viewed 59

I have a table that contains Persons Name and Country of Citizenship. A person may have a dual citizenship, but if they have US citizenship, I just want that row for the person.

Ex:\

Name   Citizenship\
John       US\
John      England\
Jim       Germany\
Mark      US\
Mark      Belgium

Expected Output

Name    Country\
John     US\
Jim      Germany\
Mark     US

Appreciate your help in advance.

union unique sql
3 Answers

You can use the ROW_NUMBER() window function, as in:

select *
from (
  select name, citizenship,
    row_number() over(partition by name 
      order by case when citizenship = 'US' then 1 else 2 end, citizenship
    ) as rn
  from t 
) x
where rn = 1
user_64361910

With NOT EXISTS:

select t.* from tablename t
where t.citizenship = 'US'
or not exists (select 1 from tablename where name = t.name and citizenship = 'US')

or with FIRST_VALUE() window function:

select distinct name,
       first_value(citizenship) 
       over (partition by name order by case when citizenship = 'US' then 1 else 2 end) citizenship
from tablename

See the demo.
Results:

> Name | Citizenship
> :--- | :----------
> John | US         
> Jim  | Germany    
> Mark | US
user_104988280

Here is one flavor:

SELECT DISTINCT
    Name,
    Citizenship= CASE WHEN COUNT(CASE WHEN Citizenship = 'US' THEN 1 ELSE 0 END) OVER (PARTITION BY Name) > 1 THEN 'US' ELSE Citizenship END
FROM 
    t
user_6115450
Related