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

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

Shorter (and correct)

You don't actually need a WHERE clause at all:

SELECT street, zip, city
FROM   address
ORDER  BY street !~~ 'Test%', ord
LIMIT  1;

!~~ is just the Postgres operator for NOT LIKE. You can use either. Note that by inverting the logic (NOT LIKE instead of LIKE), we can now use default ASCsort order and NULLs sort last, which may be important. Read on.

This is shorter (but not necessarily faster). It is also subtly different (more reliable) than the currently accepted answer by @Gordon.

When sorting by a boolean expression you must understand how it works:

• Sorting null values after all others, except special

The currently accepted answer uses ORDER BY <boolean expression> DESC, which would sort NULLs first. In such a case you should typically add NULLS LAST:

• PostgreSQL sort by datetime asc, null first?

If street is defined NOT NULL this is obviously irrelevant, but that has not been defined in the question. (Always provide the table definition.) The currently accepted answer avoids the problem by excluding NULL values in the WHERE clause.

Some other RDBMS (MySQL, Oracle, ..) don't have a proper boolean type like Postgres, so we often see incorrect advice from people coming from those products.

Your current query (as well as the currently accepted answer) need the WHERE clause - or at least NULLS LAST. With the different expression in ORDER BY neither is necessary.

More importantly, yet, if multiple rows have a matching street (which is to be expected), the returned row would be arbitrary and could change between calls - generally an undesirable effect. This query picks the row with the smallest ord to break ties and produces a stable result.

This form is also more flexible in that it does not rely on the existence of a row with ord = 0. Instead, the row with the smallest ord is picked either way.

Faster with index

(And still correct.) For big tables, the following index would radically improve performance of this query:

CREATE INDEX address_street_pattern_ops_idx ON address(street text_pattern_ops);

Detailed explanation:

• PostgreSQL LIKE query performance variations

Depending on undefined details it may pay to add more columns to the index.
The fastest query using this index:

(
SELECT street, zip, city
FROM   address
WHERE  street LIKE 'Test%'
ORDER  BY ord  -- or something else?
-- LIMIT 1  -- you *could* add LIMIT 1 in each leg
)
UNION ALL
(
SELECT street, zip, city
FROM   address
ORDER  BY ord
-- LIMIT 1  -- .. but that's not improving anything in *this* case
)
LIMIT  1

BTW, this is a single statement.

This is more verbose, but allows for a simpler query plan. The second SELECT of the UNION ALL is never executed if the first SELECT produces enough rows (in our case: 1). If you test with EXPLAIN ANALYZE, you'll see (never executed) in the query plan.

Details:

• Way to try multiple SELECTs till a result is available?

Evaluation of UNION ALL

In reply to Gordon's comment. Per documentation:

Multiple UNION operators in the same SELECT statement are evaluated left to right, unless otherwise indicated by parentheses.

Bold emphasis mine.
And LIMIT makes Postgres stop evaluating as soon as enough rows are found. That's why you see (never executed) in the output of EXPLAIN ANALYZE.

If you add an outer ORDER BY before the final LIMIT this optimization is not possible. Then all rows have to be collected to see which might sort first.

Answer 2

How about something like this... (I'm not familiar with PostgreSQL so syntax might be slightly off)

SELECT street, zip, city, 1 as SortOrder
FROM address
WHERE street LIKE 'Test%' 
-- 
union all
--
SELECT street, zip, city, 2 as SortOrder
FROM address
WHERE ord = 0
ORDER BY SortOrder
LIMIT 1;

Answer 3

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.

Answer 4

You can do the following:

SELECT street, zip, city
FROM address
WHERE (EXISTS(SELECT * FROM address WHERE street LIKE 'Test%') AND street LIKE 'Test%') OR 
      (NOT EXISTS(SELECT * FROM address  WHERE street LIKE 'Test%') AND ord = 0)