First ordered by a specific field value

Question

I have a table with three 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 a result set using priority , but first those lines that have name=core , even if they have a 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 is also a MySQL FIELD function.

If you want to do a complete sort for all possible values:

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

If you are only concerned about the fact that the "core" is the first, and other values ​​do not matter:

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

If you want to sort by "core" first, and the rest of the fields in the usual order:

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

There are some caveats here:

Firstly, I'm sure this is only a mysql function - the question is marked as mysql, but you never know.

Secondly, pay attention to how FIELD() works: it returns values ​​based on a single index - in the case of FIELD(priority, "core") it will return 1 if "core" is the value. If the field value is not in the list, it returns zero. This is why DESC necessary if you do not specify all possible values.

Answer 2

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 the comparison in MySQL is either 0 or 1 , and you can sort by this result.

Answer 3

One way to give preference to specific lines is to add a large number to your priority. You can do this with the 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

Answer 4

This works for me using Postgres 9+:

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

Answer 5

One of the methods:

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

Answer 6

 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') 

Answer 7

do the following:

 SELECT * FROM table ORDER BY column `name`+0 ASC 

Adding the +0 symbol means that:

 0, 10, 11, 2, 3, 4 

becomes:

 0, 2, 3, 4, 10, 11 

Answer 8

Use this:

 SELECT * FROM tablename ORDER BY priority desc, FIELD(name, "core")