HobbsBear HobbsBear - 29 days ago 8
MySQL Question

MySQL Order By Comma Separated List

I have the following MySQL query

SELECT * FROM Characters WHERE CharacterID IN (30, 29, 1, 292, 51)


Which returns back my list of characters as below

| CharacterID | CharacterName |
--------------------------------------
| 1 | Character 1 |
| 29 | Character 29 |
| 30 | Character 30 |
| 51 | Character 51 |
| 292 | Character 292 |


I need it to return back in the same order as the original comma seperated value. So in this specific instance, I would need it to return...

| CharacterID | CharacterName |
--------------------------------------
| 30 | Character 30 |
| 29 | Character 29 |
| 1 | Character 1 |
| 292 | Character 292 |
| 51 | Character 51 |


Is there anyway where I can specifically say something like

SELECT * FROM Characters WHERE CharacterID IN (30, 29, 1, 292, 51)
ORDER BY (CharacterID, specifically in the order of (30, 29, 1, 292, 51))

Answer

You could use order by FIELD()

SELECT * FROM Characters WHERE CharacterID IN (30, 29, 1, 292, 51)
ORDER BY FIELD(CharacterID, 30, 29, 1, 292, 51)

FIELD() is a function that returns the index position of a comma-delimited list

Comments