Order By case when then with multiple ASC/DESC

Question

Hope this question has not been ask already. My problem is :

Do you know how to do something like that

  ORDER BY 
  CASE 
    WHEN job = "jounalist" then 1, date desc 
    WHEN job = "teacher" then 2, class asc
    WHEN job = "dev" then 3, code asc 
    ELSE 4 

Answer 1

You need separate case expressions. I'm not sure what 1, 2, and 3 represent. Let me assume they are meant to put the results in the order of the CASE expressions:

ORDER BY (CASE WHEN job = 'journalist' THEN 1
               WHEN job = 'teacher' THEN 2
               WHEN job = 'dev' THEN 3
               ELSE 4
          END),
         (CASE WHEN job = 'journalist' THEN date END) DESC,
         (CASE WHEN job = 'teacher' THEN class END),
         (CASE WHEN job = 'code' THEN code END)

A CASE expression returns a single type. For this purpose, I don't recommend converting values to a single type. Instead, just use separate keys in the ORDER BY for the different groups.

I should also point out that some databases might have special functionality that would simplify some of this code.