CODEWITHSUNDEEP
mysqlsql
Alexey
Alexey

Reputation: 337

How to sort in a different way

There are countries in MySQL table:

id | title
1  | USA  
2  | Spain
3  | Italy
4  | Canada

I need to select Italy on the top of list and other countries sorted by title below. But 'order by' doesn't work.

(SELECT * FROM countries WHERE id = 3) UNION (SELECT * FROM countries WHERE id != 3 ORDER BY title)

Upvotes: 0

Views: 60

Answers (3)

Daniel Sparing
Daniel Sparing

Reputation: 2173

First sort your data based on whether it is Italy or not, getting Italy first. Then sort based on the title.

SELECT * FROM countries
ORDER BY title='Italy' DESC, title

(The only trick you have to know -- or experiment with -- is that FALSE comes before TRUE, and hence the DESC in the code. I guess that makes sense if you convert them to 0 < 1.)

Upvotes: 5

zedmartins
zedmartins

Reputation: 115

SELECT * FROM countries WHERE id = 3
union
SELECT c.* FROM (SELECT * FROM countries WHERE id != 3 order by title) c

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

you can try this

  SELECT * FROM countries 

  ORDER BY  case when id !=3  then `title` end asc ,
            case when id =3   then `title` end asc ;

Upvotes: 0

Related Questions