Arrange data in specific order

Question

I have a user table that contain 8 records. I want to arrange the data in descending order on the basis of field id (that is a primary key of that table) but except id 3 and 5. So eventually the result should be like

id  name
--  ----
3   peter
5   david
8   john
7   stella
6   jim
4   jack
2   nancy
1   scott

Except id 3 and 5 rest of the data should be arranged in descending order and 3 and 5 should come in ascending order.

Answer 1

SELECT * FROM user ORDER BY IF(id=3 OR id=5, id, ~id) ASC

Answer 2

something like this:

order by 
   case 
     when id = 3 then 999
     when id = 5 then 998
     else id
   end desc

This assumes that you really don't have more than 8 rows. Otherwise you must change the "magic" numbers that move 3 and 5 to the top.

Answer 3

I think the trick here is to use an enum.

SELECT id, name FROM my_table WHERE id IN (3, 5) ORDER BY ASC
UNION
SELECT id, name FROM my_table WHERE id NOT IN(3, 5) ORDER BY DESC