mysql order position DESC but put first if position="chef"

Question

the problem: mysql order position DESC but put first if position="chef" I want to order descending but put some things first. How is the syntax for that?

Answer 1

ORDER BY
   CASE 
     WHEN position="chef" THEN 0
     ELSE 1 
   END,
   position DESC

Adding more WHEN conditions allows you to prioritise some positions

ORDER BY
   CASE 
     WHEN position="chef" THEN 0  -- first
     WHEN position="dogsbody" THEN 99 -- last
     ELSE 1 
   END,
   position DESC

Answer 2

SELECT * FROM mytable ORDER BY column1='Put This First' DESC, column1;

This will put the rows that have column1='Put This First' first, then will proceed to order everything else based on column1.

SELECT * FROM mytable ORDER BY column1=1 DESC, column2;

This is another example. Will put rows where column1=1 first, then will order the rest of the rows based on the value of column2.