sql - Select first record if none match

Question

In PostgreSQL, I would like to select a row based on some criteria, but if no row matches the criteria, I would like to return the first row. The table actually contains an ordinal column, so the task should be easier (the first row is the one with ordinal 0). For example:

SELECT street, zip, city
FROM address
WHERE street LIKE 'Test%' OR ord = 0
LIMIT 1;

But in this case, there is no way to guarantee the order of the records that match, and I have nothing to order them by. What would be the way to do this using a single SELECT statement?

Answer 1

You are on the right track. Just add an order by:

SELECT street, zip, city
FROM address
WHERE street LIKE 'Test%' OR ord = 0
ORDER BY (CASE WHEN street LIKE 'Test%' THEN 1 ELSE 0 END) DESC
LIMIT 1;

Or, alternately:

ORDER BY ord DESC

Either of these will put the ord = 0 row last.

EDIT:

Erwin brings up a good point that from the perspective of index usage, an OR in the WHERE clause is not the best approach. I would modify my answer to be:

SELECT *
FROM ((SELECT street, zip, city
       FROM address
       WHERE street LIKE 'Test%'
       LIMIT 1
      )
      UNION ALL
      (SELECT street, zip, city
       FROM address
       WHERE ord = 0
       LIMIT 1
      )
     ) t
ORDER BY (CASE WHEN street LIKE 'Test%' THEN 1 ELSE 0 END) DESC
LIMIT 1;

This allows the query to make use of two indexes (street and ord). Note that this is really only because the LIKE pattern does not start with a wildcard. If the LIKE pattern starts with a wildcard, then this form of the query would still do a full table scan.