How to write this SQL against PG?

Question

Say, there is a table A, with columns a and b, both are int type with unique index.

I want to select from A where

• if there a tuple whose a equal to 100, then return this tuple
• otherwise, return a tuple whose b is smallest and less than 10000

This can be easily done with two SQL statements:

select * from A where a = 100

If first SQL returns nothing, then run:

select * from A where b < 10000 order by b limit 1

But I want to implement this logic in a single SQL statement. Is it possible?

Answer 1

with a100orminb as
(
  select *, 1 as priority from A where a = 100
  union all
  select *, 2 as priority from A where b < 10000 order by b limit 1
)
select * from a100orminb order by priority limit 1;

You'll get unneeded column "priority", if you don't want to list all columns in the SQL, and use *, but I suppose that it is not a problem.

Answer 2

I don't know why you want that to be one statement, but UNION ALL, sorting and a LIMIT should do the trick:

(SELECT *, FALSE AS differs_from_100
FROM a WHERE a = 100
UNION ALL
SELECT *, TRUE AS differs_from_100
FROM a WHERE b < 10000)
ORDER BY differs_from_100, b
FETCH FIRST 1 ROWS ONLY;

The disadvantage is that you have to execute the second statement even if the first returns a result, so running two statements might be preferable.

A more complicated version that might perform better would be

WITH has_100 AS (
   SELECT *
   FROM a WHERE a = 100
)
SELECT * FROM has_100
UNION ALL
SELECT *
FROM a WHERE b < 10000
WHERE NOT EXISTS (SELECT FROM has_100)
ORDER BY b
FETCH FIRST 1 ROWS ONLY;