Returning query results in predefined order

by

I didn’t think this was possible, but found a blog entry here that seems to do the type of thing you’re after:

SELECT id FROM table WHERE id in (7,2,5,9,8) 
ORDER BY FIND_IN_SET(id,"7,2,5,9,8");

will give different results to

SELECT id FROM table WHERE id in (7,2,5,9,8) 
ORDER BY FIND_IN_SET(id,"8,2,5,9,7");

FIND_IN_SET returns the position of id in the second argument given to it, so for the first case above, id of 7 is at position 1 in the set, 2 at 2 and so on – mysql internally works out something like

id | FIND_IN_SET
---|-----------
7  | 1
2  | 2
5  | 3

then orders by the results of FIND_IN_SET.

More Related Contents:

  1. MySQL order by before group by
  2. Why does MYSQL higher LIMIT offset slow the query down?
  3. MySQL ‘Order By’ – sorting alphanumeric correctly
  4. MySQL Orderby a number, Nulls last
  5. How does MySQL process ORDER BY and LIMIT in a query?
  6. Ordering by the order of values in a SQL IN() clause
  7. Order a MySQL table by two columns
  8. Ordering by specific field value first
  9. MySQL “Group By” and “Order By”
  10. Using union and order by clause in mysql
  11. SQL order string as number
  12. GROUP_CONCAT ORDER BY
  13. What is MySQL row order for “SELECT * FROM table_name;”?
  14. Slow query when using ORDER BY
  15. Select the 3 most recent records where the values of one column are distinct
  16. MySQL – Control which row is returned by a group by
  17. MySQL: Sort GROUP_CONCAT values
  18. MySQL order by “best match”
  19. Order by COUNT per value
  20. MySQL/MariaDB – order by inside subquery
  21. MySQL Order before Group by
  22. SQL Order By Count
  23. ORDER BY date and time BEFORE GROUP BY name in mysql
  24. Mysql delete statement with limit
  25. MySQL – ORDER BY values within IN()
  26. Mysql: Order by like?
  27. Sort the rows according to the order specified in WHERE IN clause
  28. ORDER BY alphabet first then follow by number
  29. MYSQL Select One Random record from each Category
  30. mysql order by, null first, and DESC after

Leave a Comment