Mysql SQL: a specific item should be first, and then sort the remaining items

Question

Say I have a table below.

I want to get all friends, but I want identifier 5 to be the first item in the list. I don't care that I get the rest of the items.

The desired query result will be:

friends ------- id name 5 nahum 1 moshe 2 haim 3 yusuf 4 gedalia 6 dana 

How can i do this?

using mysql 5.1.x.

Thank!

Answer 1

 select id,name from friends order by id=5 desc 

(given that you do not care about the order of the others, otherwise, for example, relax by id asc)

 select id,name from friends order by id=5 desc, id asc 

Answer 2

Try the following:

 select id,name from friends order by case when id=5 then -1 else id end 

if you have more than what you can do:

 select id,name from friends order by case when id in (5,15,25) then -1 else id end,id 

Answer 3

Now I can’t access MySQL for testing, so it could be undone ... but you can use the fact that Booleans are also sorted and can have multiple sort fields.

 SELECT ... ORDER BY id != 5, id 

(You may need to write id = 5 , I cannot remember if TRUE is sorted before or after FALSE.)

EDIT: Oh, I just read that you don't care about the order of the others, and in this case, I heartily recommend @Richard's answer.

Answer 4

If you want to do the same for a UNION request, for example, if you have:

 select id,name from friends UNION select id,name from friends order by id=5 desc 

... you will get an exception in PostgreSQL:

You can use column names only for the result, not expressions or functions. TIP: add an expression / function to each SELECT or move UNION to the from clause

To get around this, you should use the following:

 select id,name, (id=5) AS is_five from friends UNION select id,name, (id=5) AS is_five from friends order by is_five DESC, id DESC 

The expression (id = 5) will return 't' OR 'f', depending on whether the column value is equal to or not the expected value (5), so the order will order the columns 't' first, then the rest.

Answer 5

This is a little ugly because it has code duplication, but it does the trick:

 select .... where id = 5 union select .... where not id = 5