MySQL sort by some list

by

Since 1 < 3 < 77 < 123, a simple ORDER BY id would suffice.

If, however, you want to order this way: 77, 3, 123, 1, then you could use function FIELD():

SELECT id, name
FROM mytable 
WHERE id IN (77, 3, 123, 1) 
ORDER BY FIELD(id, 77, 3, 123, 1)

If your query matches more rows than you list in FIELD

FIELD returns 0 when a row does not match any of the ids you list, i.e. a number smaller than the numbers returned for listed ids. This means, if your query matches more rows than the ones you list, those rows will appear first. For example:

SELECT id, name
FROM mytable 
WHERE id IN (77, 3, 123, 1, 400) 
ORDER BY FIELD(id, 77, 3, 123, 1)

In this example, the row with ID 400 will appear first. If you want those rows to appear last, simply reverse the list of IDs and add DESC:

SELECT id, name
FROM mytable 
WHERE id IN (77, 3, 123, 1, 400) 
ORDER BY FIELD(id, 1, 123, 3, 77) DESC

More Related Contents:

  1. Search engine PHP return error [closed]
  2. UTF-8 all the way through
  3. SQL injection that gets around mysql_real_escape_string()
  4. Can I mix MySQL APIs in PHP?
  5. How to include a PHP variable inside a MySQL statement
  6. jQuery Validate remote method usage to check if username already exists
  7. MySQL query to get column names?
  8. How do you connect to multiple MySQL databases on a single webpage?
  9. Warning: mysqli_connect(): (HY000/1045): Access denied for user ‘username’@’localhost’ (using password: YES)
  10. Achieve hierarchy, Parent/Child Relationship in an effective and easy way
  11. PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers
  12. Insert/update helper function using PDO
  13. How can I write SQL for a table that shares the same name as a protected keyword in MySql? [duplicate]
  14. MySQLi count(*) always returns 1
  15. Formulas to Calculate Geo Proximity
  16. How do I insert multiple checkbox values into a table?
  17. Get all child, grandchild etc nodes under parent using php with mysql query results
  18. What are the differences between backtick and single quote? Can I use IF statement in a query as above?
  19. Laravel Migration table already exists, but I want to add new not the older
  20. Saving images in database mysql
  21. convert date string to mysql datetime field
  22. Set PDO to throw exceptions by default [duplicate]
  23. I cannot use mysql_* functions after upgrading PHP
  24. PHP Laravel: No connection could be made because the target machine actively refused it
  25. SQL Query with binary data (PHP and MySQL)
  26. How do I format numbers to have only two decimal places?
  27. How to get last inserted id from table MySQL [duplicate]
  28. Group mysql results by category and display them into groups under each category
  29. Save array in mysql database
  30. PHP & mySQL: When exactly to use htmlentities?

Leave a Comment