Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

sql - Select first record if none match

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...