mysql SQL: specific item to be first and then to sort the rest of the items

Question

Lets say I have the table below.

I want to get all the friends, but I want the id 5 to be the first item in the list. I don't care about the order that I receive 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.

Thanks!

Answer 1

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

(given you don't care about order of the rest, otherwise, e.g. rest by id asc)

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

Answer 2

Try this:

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

if you have more then one 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

I can't access a MySQL now to test, so it might be reversed... but you can use the fact that Booleans also sort, and that you can have several sort fields.

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

(you might have to write id = 5, I can't remember if TRUEs sort before or after FALSEs.)

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

Answer 4

You can use field() in MySQL.

select id,name from friends order by field(id,5,id)

The 1st parameter in field() means the field you want to sort with, the rest is ordering.

So 5 will be sort first, and the rest from id (without 5). You can do like field(id,5,1,3,id) if you want 5,1,3 to be in front.

5 can be choose to sort at last by field(id,id,5). The 2nd id will exclude 5 from it also.

Answer 5

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

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

... you would get an exception in PostgreSQL:

Only result column names can be used, not expressions or functions. HINT: Add the expression/function to every SELECT, or move the UNION into a from clause

To get around this, you would 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) would return 't' OR 'f', depending on whether your column value is equal or not to the expected value (5), so the order by would first order the 't' columns, then the rest.

Answer 6

You should use MySQL's ORDER BY FIELD clause to solve this. Although, the answer has been accepted on this, here's a better solution.

select 1 id, 'Zeta' order_col union all
select 2 id, 'Alpha' order_col union all
select 3 id, 'Gamma' order_col union all
select 4 id, 'Phi' order_col union all
select 5 id, 'Delta' order_col union all
select 6 id, 'Delta' order_col union all
select 7 id, 'Alpha' order_col union all
select 8 id, 'Gamma' order_col union all
select 9 id, 'Zeta' order_col union all
select 10 id, 'Phi' order_col 
order by field (order_col, 'Alpha', 'Gamma', 'Phi', 'Delta', 'Zeta'), id;

This is better than

• id=something, order by id asc
• order by case when something then 1 when something_else then 2 end desc

Answer 7

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