CODEWITHSUNDEEP
mysqlsqlselectsql-order-by
Omid
Omid

Reputation: 4705

Ordering by specific field value first

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

Upvotes: 158

Views: 196416

Answers (8)

Saquib Azam
Saquib Azam

Reputation: 83

Use this: 

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

Upvotes: -1

Nerdmaster
Nerdmaster

Reputation: 4555

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.

Upvotes: 273

meiy arul
meiy arul

Reputation: 19

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

Upvotes: 1

Madhuka Dilhan
Madhuka Dilhan

Reputation: 1416

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

Upvotes: 0

juergen d
juergen d

Reputation: 204746

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.

Upvotes: 106

Vojtech Vitek - golang.cz
Vojtech Vitek - golang.cz

Reputation: 27726

This works for me using Postgres 9+:

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

Upvotes: 6

Icarus
Icarus

Reputation: 63956

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

Upvotes: 3

mellamokb
mellamokb

Reputation: 56769

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

Upvotes: 6

Related Questions