How can I sort mySQL data with a specific country ID first, followed by the normal order?

Question

I have a mySQL data set like this:

id      | name      | country_id      | sort    |
-------------------------------------------------
0000001   Name A      2001              29.90
0000002   Name B      2929              90.02
0000003   Name C      2001              99.50
0000004   Name D      2001              42.03
0000005   Name E      2929              62.49
0000006   Name F      1005              78.00

Normally, I would do an ORDER BY sort DESC and then I'd get a result like:

0000003   Name C      2001              99.50
0000002   Name B      2929              90.02
0000006   Name F      1005              78.00
0000005   Name E      2929              62.49
0000004   Name D      2001              42.03
0000001   Name A      2001              29.90

Now, what I'd like to do is show the results of country_id 2929 first, also ordered by sort DESC, followed by the other results in the sort DESC order as they would if there was no country_id order before.

How would I achieve that? So basically the order should be:

0000002   Name B      2929              90.02
0000005   Name E      2929              62.49
0000003   Name C      2001              99.50
0000006   Name F      1005              78.00
0000004   Name D      2001              42.03
0000001   Name A      2001              29.90

Have look here:[http://stackoverflow.com/questions/14104055/ordering-by-specific-field-value-first](http://stackoverflow.com/questions/14104055/ordering-by-specific-field-value-first) — Tzoreol, Dec 18 '15 at 13:27

score 3 · Accepted Answer · answered Dec 18 '15 at 13:25

3

use order by FIELD

order by FIELD(country_id,'2929') DESC,sort DESC

answered Dec 18 '15 at 13:25

Abhishek Sharma

6,689
1
14
20

score 0 · Answer 2 · answered Dec 18 '15 at 13:29

0

try;

select
    id ,
    name ,
    country_id ,
    sort
from tbl
order by (case when country_id = 2929 then 1 else 0 end) DESC, sort DESC

answered Dec 18 '15 at 13:29

Praveen

8,945
4
31
49

score 0 · Answer 3 · answered Dec 18 '15 at 13:38

0

Or another form of the previous answer:

select id , name , country_id , if(country_id = 2929,0,1) as Country_Sort, sort from tbl order by Country_Sort, sort DESC

answered Dec 18 '15 at 13:38

L Q

81
4

score 0 · Answer 4 · answered Dec 18 '15 at 13:38

0

The shortest form is:

order by country_id=2929 DESC, sort DESC

country_id=2929 will be evaluated to 1 when it's true, and to 0 otherwise, so you can just order by country_id=2929 DESC to put the country 2929 at the top.

answered Dec 18 '15 at 13:38

fthiella

48,073
15
90
106

How can I sort mySQL data with a specific country ID first, followed by the normal order?

4 Answers4