How can I fetch the data via select and subselect or join statement

I have these data in the postgres DB:

id type  repair status 
1   0     open
1   1     repaired
2   0     open
3   0     open
3   1     repaired
3   1     repaired
4   1     repaired
5   0     open
5   1     open
5   1     repaired
5   0     open
5   1     repaired
6   1     repaired
6   1     open

I want to fetch status of all id’s (id can be some thing like book etc.). The type column means: 0 is not bookable, 1: is bookable.

Example id:5 i want to get only the type not bookable, because there are dataset repair status open and the thing is not bookable.

Example id:6 I want to get only the type bookable, because in spite of repair status open the thing is bookable.

can i do it via one select query (ths query can contain subqueries or/and joins)?

I expect this result:

id type  repair status 
1   0 open
2   0 open
3   0 open
4   1 repaired
5   0 open
6   1 open

Answer

If you want one row per id in Postgres, the best solution is distinct on. The only question is your order by clause.

Your rules are rather hard to decipher. My best guess is:

select distinct on (id) t.*
from t
order by id, bookable desc, (status = 'open')::int desc;

In standard SQL, you would change the order by to:

order by id, bookable desc,
         (case when status = 'open' then 1 else 0 end)

Advertisement

Answer