Ordering by specific field value first
MysqlSqlSelectSql Order-ByMysql Problem Overview
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
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
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.
Solution 3 - Mysql
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
Solution 4 - Mysql
This works for me using Postgres 9+:
SELECT *
FROM your_table
ORDER BY name = 'core' DESC, priority DESC
Solution 5 - Mysql
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
Solution 6 - Mysql
SELECT * FROM cars_new WHERE status = '1' and car_hide !='1' and cname IN ('Executive Car','Saloon','MPV+','MPV5') ORDER BY FIELD(cname, 'Executive Car', 'Saloon','MPV+','mpv5')
Solution 7 - Mysql
do this:
SELECT * FROM table ORDER BY column `name`+0 ASC
Appending the +0 will mean that:
0, 10, 11, 2, 3, 4
becomes :
0, 2, 3, 4, 10, 11
Solution 8 - Mysql
Use this:
SELECT *
FROM tablename
ORDER BY priority desc, FIELD(name, "core")