Postgres 11 - *prioritized* where clause in group table below is given:

create table cafes (
  id uuid not null
    constraint cafes_pkey primary key,
  identity uuid not null,

  is_closed boolean,
  is_illegal boolean
)


The problem is to select one cafe from each group (grouped by identity), which:


priority #1: has is_closed = true
priority #2: has is_illegal = true


That is, if we have a closed cafe, we select it. If there are no such cafes in group, we search by is_illegal = true.

Any ideas how to solve this problem? :)
Booleans can be ORDERED BY just like other types, with false coming before true when no direction is given.

You could use this in a DISTINCT ON, or in a LATERAL join.

SELECT DISTINCT ON (identity), * 
from cafes 
ORDER BY identity, is_closed DESC NULLS last
       , is_illegal DESC NULLS last