Daniel Daniel - 7 months ago 22
SQL Question

SQL - select first in value set by priority?

I'm fairly inexperienced in SQL and this seems like it must be an easy task, but I'm not sure how to go about it.

Basically I want to select a single row from table A where field "someField" is in a pre-determined set "someSet", but I want it to look for each value in the set individually. For example, let's say "someSet" contains 5, 6, 9, 3. I would use a query similar to this:

SELECT * FROM A WHERE someField IN (5, 6, 9, 3) LIMIT 1


However, I want it to look for 5 first, then 6, then 9, then finally 3 if no rows have been found yet. Written as separate queries it'd look like this:

SELECT * FROM A WHERE someField = 5 LIMIT 1
(if no results returned)
SELECT * FROM A WHERE someField = 6 LIMIT 1
(if no results returned)
SELECT * FROM A WHERE someField = 9 LIMIT 1
(if no results returned)
SELECT * FROM A WHERE someField = 3 LIMIT 1


Obviously using 4 queries (theoretically infinite queries) isn't very elegant, is there a way to make this into a single query?

Answer

You can do

SELECT * FROM A WHERE someField IN (5, 6, 9, 3) 
ORDER BY FIELD( someField, 5, 6, 9, 3) 
LIMIT 1
Comments