Sort the rows according to the order specified in WHERE IN clause

user_378861 0
Viewed 60166

I have something like:

SELECT *
FROM table
WHERE id IN (118, 17, 113, 23, 72);

It returns the rows ordered by ID, ascending. Is there a way to get back the rows in the order specified in the IN clause?

sql-order-by mysql sql
6 Answers

Set based approach: create a table-like structure that contains the id values and sort order, and join:

SELECT tbl.*
FROM (VALUES
    ROW(118, 1),
    ROW(17,  2),
    ROW(113, 3),
    ROW(23,  4),
    ROW(72,  5)
) AS tvc(id, sort)
JOIN tbl ON tvc.id = tbl.id
ORDER BY tvc.sort
user_870150
Related