問題描述
為 MySQL 選擇查詢自定義 order by (Customize order by for MySQL select query)
I want to do something like this: select * from table order by id asc with the exception that if the id is 5 (for example) make it be top, basically 5 then all other IDs ordered asc.
How can I do this please?
Thank you.
參考解法
方法 1:
You can also use function FIELD():
SELECT *
FROM table
ORDER BY FIELD(id, 5) DESC
, id ASC
Especially useful if you want to have first the rows with say, id = 5, 23, 17, you can use:
SELECT *
FROM table
ORDER BY FIELD(id, 17, 23, 5) DESC
, id ASC
方法 2:
You can use UNION as initally suggested by me, with a sorting on both columns as suggested by @Mike in the comments.
(SELECT *, 1 single_id FROM table_name WHERE id = 5)
UNION ALL
(SELECT *, 2 all_ids FROM table_name WHERE id <> 5)
ORDER BY single_id, id
Or better off with an IF statement, to avoid the overhead of two sorts:
SELECT *, IF(id = 5, -1, id) ordering
FROM table_name
ORDER BY ordering ASC
方法 3:
SELECT *, CASE WHEN id = 5 THEN -1 ELSE id END AS ordering
FROM table
ORDER BY ordering ASC
方法 4:
SELECT * FROM table_name ORDER BY id=7 DESC, id ASC
Since this doesn't use indexes, I don't recommend using it on large tables.
(by Francisc、ypercubeᵀᴹ、Shef、devin、Kaivosukeltaja)