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? 🙂