Automatic order by when using IN condition mariadb

Question

Let's say I have the following database table named 'ingredients':

+----+--------------+---------+
| id | ingredientID |  name   |
+----+--------------+---------+
|  4 |            1 | Pepper  |
|  5 |            3 | Onion   |
|  6 |            9 | Mustard |
|  7 |            6 | Tomato  |
+----+--------------+---------+

If I use this query:

SELECT * FROM ingredients WHERE ingredientID IN(1,6,3,9)

I get the result back ordered by its id: Pepper, Onion, Mustard, Tomato.

However I want to get the result back in the order I asked them: Pepper, Tomato, Onion, Mustard.

How would I go about achieving this?

Answer 1

With the function FIELD():

SELECT * FROM ingredients 
WHERE ingredientID IN (1,6,3,9)
ORDER BY FIELD(ingredientID,1,6,3,9) 

See the demo.
Results:

| id  | ingredientID | name    |
| --- | ------------ | ------- |
| 4   | 1            | Pepper  |
| 7   | 6            | Tomato  |
| 5   | 3            | Onion   |
| 6   | 9            | Mustard |