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
This question is related to
mysql
sql
select
sql-order-by
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
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.
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')
Use this:
SELECT *
FROM tablename
ORDER BY priority desc, FIELD(name, "core")
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
This works for me using Postgres 9+:
SELECT *
FROM your_table
ORDER BY name = 'core' DESC, priority DESC
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
Source: Stackoverflow.com