Ordering by specific field value first

Question

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.

One way to give preference to specific rows is to add a large number to their priority. You can do this with a CASE statement:

  select id, name, priority
    from mytable
order by priority + CASE WHEN name='core' THEN 1000 ELSE 0 END desc

Demo: http://www.sqlfiddle.com/#!2/753ee/1

This works for me using Postgres 9+:

SELECT *
FROM your_table
ORDER BY name = 'core' DESC, priority DESC

One way is this:

select id, name, priority from table a
order by case when name='core' then -1 else priority end asc, priority asc