Ordering by specific field value first

Question

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

score 255 · Accepted Answer · edited Jan 11 '19 at 20:44

255

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.

edited Jan 11 '19 at 20:44

Joey Bass

3
2

answered Aug 21 '14 at 18:07

Nerdmaster

4,287
1
22
16

8

After about 5 years I changed accepted answer to this one because it's cleaner and faster. – Omid Aug 15 '17 at 12:21
1

db2 equivalent? – Cybermonk Nov 17 '17 at 10:44
It worked for me, would like to ask one more question about how to handle If column 'priority' contains values like ex: 'earth core','new board' etc. Here column not containing an exact value, can we write something like %core%? – Jayanth Suvarna Jun 20 '18 at 09:41
@JayanthSuvarna: looking at the MySQL FIELD() docs, I am pretty sure there isn't any way to evaluate this as substrings, as each argument has to be some kind of string. There may be some string manipulation functions that could help, but I'm not sure. – Nerdmaster Aug 08 '18 at 18:48
Thanks, mate. You made my day. – BEingprabhU Sep 12 '18 at 10:36
@Omid - There are three answers here, each produces a different result. Which one did you find "cleaner and faster"? – Rick James Oct 16 '20 at 14:28
@RickJames In comparison to this answer: https://stackoverflow.com/a/14104090/550042. – Omid Oct 16 '20 at 15:38
@Omid - Good. I like that one, too. I hope that is the one Leia wants to reward. I don't think any other version will be faster. – Rick James Oct 16 '20 at 21:40
I feel so weird, in my 21 years career I never had to do this! Now all of a sudden this saves me a bunch of unnecessary queries! Thanks for teaching me something new! :-) – GTodorov Jan 20 '21 at 23:47

juergen d · Answer 2 · 2016-03-03T14:33:39.540

104

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.

edited Mar 03 '16 at 14:33

answered Dec 31 '12 at 16:52

juergen d

201,996
37
293
362

1

what does `1` and `2` means here? – Niraj Chauhan Dec 24 '13 at 13:42
1

I've around 3000 rows to sort. In my case the solution of @Ayman-Hourieh on http://stackoverflow.com/questions/958627/mysql-order-by-values-within-in takes half of the time compared to this solution. – nightlyop Feb 25 '14 at 09:15
@nightlyop: Good one. Only one note: The faster solution is MySQL-specific. – juergen d Apr 10 '15 at 21:28
`1` and `2` are just 2 numbers I use to sort the data. Could be `3` and `4` or something else. – juergen d Nov 29 '16 at 10:11
What about when there is `%` in the `WHERE` clause? Like `. . . WHERE name LIKE '%sth%' . . .` ? http://stackoverflow.com/questions/41303379/how-can-i-sort-the-result-of-a-query-based-on-the-one-column – stack Dec 23 '16 at 14:44
@stack: Not really related to this question. Ask another question with detailed explanation of your problem. – juergen d Dec 23 '16 at 14:46
@juergend [I did](http://stackoverflow.com/questions/41303379/how-can-i-sort-the-result-of-a-query-based-on-the-one-column) – stack Dec 23 '16 at 14:49
Your method #1 is more useful than using FIELD() as you can add entries at the top of your results that aren't in DESC order. – will Jul 02 '21 at 22:52

score 6 · Answer 3 · answered Dec 31 '12 at 16:52

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

score 6 · Answer 4 · answered Nov 27 '15 at 20:11

6

This works for me using Postgres 9+:

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

answered Nov 27 '15 at 20:11

Vojtech Vitek - golang.cz

25,275
4
34
40

Care to explain -1? Error + Postgres version? – Vojtech Vitek - golang.cz Sep 26 '16 at 20:19

Icarus · Answer 5 · 2012-12-31T16:57:10.753

3

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

edited Dec 31 '12 at 16:57

answered Dec 31 '12 at 16:52

Icarus

63,293
14
100
115

1

Won't this lose the order of the `core` rows? – mellamokb Dec 31 '12 at 16:54

score 1 · Answer 6 · edited Nov 26 '16 at 09:48

1

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

edited Nov 26 '16 at 09:48

FelixSFD

6,052
10
43
117

answered Nov 26 '16 at 09:45

meiy arul

19
1

3

Although your code may be the answer to the question, it's better to provide some explanation about it. – Mehraban Nov 26 '16 at 10:09

score 0 · Answer 7 · answered Jun 23 '16 at 09:44

0

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

answered Jun 23 '16 at 09:44

Madhuka Dilhan

1,396
1
14
21

This technique is for turning a string into a number. It does not address the OP's question. (However, without seeing the datatype of `priority`, I can't say whether it _should be_ part of the complete solution.) – Rick James Oct 16 '20 at 14:25

score -1 · Answer 8 · edited Sep 23 '19 at 08:24

-1

Use this:

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

edited Sep 23 '19 at 08:24

jva

2,797
1
26
41

answered Sep 23 '19 at 08:01

Saquib Azam

73
1
4

This should have been `ORDER BY FIELD(name, "core") DESC, priority ASC`, so that we first look at the "name" field, and place the "core" values first. – Will59 May 19 '23 at 13:08

Ordering by specific field value first

8 Answers8

Linked

Related