CODEWITHSUNDEEP
mysqlsqlsql-order-by
Francisc
Francisc

Reputation: 80385

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.

Upvotes: 2

Views: 194

Answers (4)

Shef
Shef

Reputation: 45589

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

Upvotes: 3

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

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

Upvotes: 4

Kaivosukeltaja
Kaivosukeltaja

Reputation: 15735

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.

Upvotes: 2

devin
devin

Reputation: 194

SELECT *, CASE WHEN id = 5 THEN -1 ELSE id END AS ordering 
FROM table 
ORDER BY ordering ASC

Upvotes: 3

Related Questions