Ordering by specific field value first
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.
Generally you can do
select * from your_table
order by case when name = 'core' then 1 else 2 end,
priority
Especially in MySQL you can also do
select * from your_table
order by name <> 'core',
priority
Since the result of a comparision in MySQL is either 0
or 1
and you can sort by that result.