Ordering by the order of values in a SQL IN() clause

by

Use MySQL’s FIELD() function:

SELECT name, description, ...
FROM ...
WHERE id IN([ids, any order])
ORDER BY FIELD(id, [ids in order])

FIELD() will return the index of the first parameter that is equal to the first parameter (other than the first parameter itself).

FIELD('a', 'a', 'b', 'c')

will return 1

FIELD('a', 'c', 'b', 'a')

will return 3

This will do exactly what you want if you paste the ids into the IN() clause and the FIELD() function in the same order.

More Related Contents:

  1. How does MySQL process ORDER BY and LIMIT in a query?
  2. Ordering by specific field value first
  3. MySQL “Group By” and “Order By”
  4. Using union and order by clause in mysql
  5. SQL order string as number
  6. MySQL order by “best match”
  7. Order by COUNT per value
  8. MySQL/MariaDB – order by inside subquery
  9. Mysql: Order by like?
  10. Sort the rows according to the order specified in WHERE IN clause
  11. ORDER BY alphabet first then follow by number
  12. MYSQL Select One Random record from each Category
  13. How can I return pivot table output in MySQL?
  14. Using LIMIT within GROUP BY to get N results per group?
  15. MySQL error: key specification without a key length
  16. Insert into multiple tables in one query
  17. generate_series() equivalent in MySQL
  18. MySQL: Can’t create table (errno: 150)
  19. Mysql query to dynamically convert rows to columns
  20. Why does MySQL report a syntax error on FULL OUTER JOIN?
  21. Error creating table: You have an error in your SQL syntax near ‘order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ‘ at line 1 [duplicate]
  22. Mixing ANSI 1992 JOINs and COMMAs in a query
  23. SQL: Creating a Relational table with 2 different auto_increment
  24. MySQL variable format for a “NOT IN” list of values
  25. Convert partially non-numeric text into number in MySQL query
  26. MySQL Select last 7 days
  27. Second SELECT query if first SELECT returns 0 rows
  28. How to retrieve JSON data from MySQL?
  29. Convert SQL Server query to MySQL [duplicate]
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment