MysqlSQL: 特定的条目应该是第一个，然后对其余的条目进行排序

假设我有下面这张桌子。

我想得到所有的朋友，但我希望 ID 5是列表中的第一个项目。我不在乎我收到其余物品的顺序。

所需的查询结果将是:

friends
-------


id    name


5     nahum
1     moshe
2     haim
3     yusuf
4     gedalia
6     dana

我怎么能这么做？

使用 Mysql 5.1. x。

谢谢！

74369 次浏览
小开

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
小开

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
小开
最佳答案 
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
小开

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.

小开

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.

小开

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
小开

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.


提交答案