CODEWITHSUNDEEP
sqlpostgresql
rpm787
rpm787

Reputation: 81

Filtering postgres value based on certain criteria

I have a postgresql table on postgres 11 where I have a column of people and a column of their preferred airlines.

Name | Airlines
----------------
Patrick | Delta
Patrick | United
Patrick | American
James   | Delta
Sophie  | United
Sophie  | American
John    | United

I need to have the values in a table where if someone has Delta in their preferred airlines, then they have Delta, otherwise they get Other-Airlines like this, desired result:

Name | Airlines
----------------
Patrick | Delta
James   | Delta
Sophie  | Other-Airlines
John    | Other-Airlines.

Is there a way to filter this out easily ? I know with numerical or alphabetical values it's easy to use min, max, or something to that effect.

Thank you very much.

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269573

You can use group by:

select name,
       (case when count(*) filter (airline = 'Delta') > 0
             then 'Delta' else 'Other airline'
        end) as preferred
from t
group by name;

You can also use distinct on:

select distinct on (name) name,
       (case when airline = 'Delta' then airline else 'Other airline'
        end) as preferred
from t
order by name, (airline = 'Delta') desc

Upvotes: 1

Related Questions