What I would like to achieve, in a single query, is to return the id of a record that matches a certain criterion, and if nothing is found, another criterion. So these two criteria have different priorities and a simple 'or' will not do.
I came up with two solutions but wonder whether this could be simplified:
select id,1 as n from <tablename> where <criterion1>
union select id,2 as n from <tablename> where <criterion2>
order by n asc limit 1;
or:
select coalesce ((select id from <tablename> where <criterion1>),
(select id from <tablename> where <criterion2>));
What I'd prefer is not to have to repeat the communalities of the two queries (in reality I select more columns, so would have to maintain these in parallel), and both queries concern the same table.
In case there is a shortcut I'd be happy to learn about it.
If you want one row, use order by and limit:
select t.*
from t
where <criterion1> or <criterion2>
order by (case when <criterion1> then 1 else 2 end)
limit 1;