Ordering by specific field value first

Question

I have a table with 3 columns:

id	name	priority
1	core	10
2	core	9
3	other	8
4	board	7
5	board	6
6	core	4

I want to order the result set using priority but first those rows that have name=core even if have lower priority. The result should look like this

id	name	priority
6	core	4
2	core	9
1	core	10
5	board	6
4	board	7
3	other	8

Answer 1

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.