CODEWITHSUNDEEP
mysqlsql
Daniel
Daniel

Reputation: 660

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?

Upvotes: 3

Views: 4569

Answers (1)

Yisroel
Yisroel

Reputation: 8174

You can do 

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

Upvotes: 7

Related Questions