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 p开发者_开发知识库re-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?
You can do
SELECT * FROM A WHERE someField IN (5, 6, 9, 3)
ORDER BY FIELD( someField, 5, 6, 9, 3)
LIMIT 1
精彩评论