Daniel - 9 months ago 51

SQL Question

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
```