Ordering by specific field value first

by

There’s also the MySQL FIELD function.

If you want complete sorting for all possible values:

SELECT id, name, priority
FROM mytable
ORDER BY FIELD(name, "core", "board", "other")

If you only care that “core” is first and the other values don’t matter:

SELECT id, name, priority
FROM mytable
ORDER BY FIELD(name, "core") DESC

If you want to sort by “core” first, and the other fields in normal sort order:

SELECT id, name, priority
FROM mytable
ORDER BY FIELD(name, "core") DESC, priority

There are some caveats here, though:

First, I’m pretty sure this is mysql-only functionality – the question is tagged mysql, but you never know.

Second, pay attention to how FIELD() works: it returns the one-based index of the value – in the case of FIELD(priority, "core"), it’ll return 1 if “core” is the value. If the value of the field is not in the list, it returns zero. This is why DESC is necessary unless you specify all possible values.

More Related Contents:

  1. Recreating a query using INNER JOIN? [duplicate]
  2. What is better subquery vs literal on IN clause in mysql
  3. SQL query return data from multiple tables
  4. quick selection of a random row from a large table in mysql
  5. MySQL SELECT only not null values
  6. How does MySQL process ORDER BY and LIMIT in a query?
  7. Ordering by the order of values in a SQL IN() clause
  8. MySQL “Group By” and “Order By”
  9. Using union and order by clause in mysql
  10. SQL order string as number
  11. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  12. What is MySQL row order for “SELECT * FROM table_name;”?
  13. MySQL order by “best match”
  14. Order by COUNT per value
  15. MySQL/MariaDB – order by inside subquery
  16. Checking multiple columns for one value
  17. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  18. MySQL INSERT INTO … VALUES and SELECT
  19. MySQL error 1241: Operand should contain 1 column(s)
  20. How to select one row randomly taking into account a weight?
  21. Compare only day and month with date field in mysql
  22. Syntax error when using except in a query
  23. MySQL INNER JOIN select only one row from second table
  24. Mysql: Order by like?
  25. MySQL Select Query – Get only first 10 characters of a value
  26. Sort the rows according to the order specified in WHERE IN clause
  27. SQL query to check if a name begins and ends with a vowel
  28. How to SELECT by MAX(date)?
  29. ORDER BY alphabet first then follow by number
  30. MYSQL Select One Random record from each Category

Leave a Comment