Red Whale Red Whale - 3 months ago 6
MySQL Question

Always at the bottom if id='3' (use `ORDER BY` in MySQL)

id
----
0
2
1
3
7
1
6
3
1


I want to sort the
id
value in descending order by using
ORDER BY
in MySQL.

But if
id
value equals
3
, always put it to the bottom.

The result will be like:

id
----
7
6
2
1
1
1
0
3
3


How should I do?

My try:

SELECT id FROM `table` ORDER BY CASE WHEN id='3' THEN ??? END, id DESC


What should the question marks be?

Answer

In addition to Tim's answer I would like to play around with boolean value:

SELECT
    id
FROM
    `table`
ORDER BY (id = 3), id DESC

Since MySQL boolean expression resolves into 0/1 so that we can play with that in this particular scenario.

Look when the id is equal to 3 then the order by looks like ORDER BY 1, <id> DESC.

And for any other id other than 3 it looks like ORDER BY 0, <id> DESC

Demo here:

SQLFiddle

Comments