CASE expressions in the ORDER BY clause

Using a CASE expression in the ORDER BY clause is a technique that allows us to obtain custom sequencing when the natural values in the table columns are not themselves adequate for the task.

I'll give two examples.

The first example is for a situation where the Human Resources department wants to see a list of salaries by position, except that they want salespeople first, IT staff second, management third, and everybody else last, with of course the individuals within each group listed in descending order by salary.

The CASE expression is used to "translate" the positions into values that meet the sort sequence requirements.

SELECT position
     , empno
     , salary
  FROM personnel
ORDER
    BY CASE 
         WHEN position = 'sales' THEN 1
         WHEN position = 'it'    THEN 2
         WHEN position = 'mgmt'  THEN 3
                                 ELSE 4 END
     , salary DESC

The CASE expression actually creates an additional separate column that is "appended" to the other columns extracted from the table. The CASE expression calculates the value of this additional column for each row. This additional column allows the result set to be sorted, but it is not returned in the result set (unless you also include the CASE expression in the SELECT clause). The CASE expression is needed because the position names do not sort into the right sequence, neither ASC nor DESC, on their own.

The second example is very similar:

SELECT position
     , empno
     , salary
  FROM personnel
ORDER
    BY CASE 
         WHEN position = 'sales' THEN 'Curly'
         WHEN position = 'it'    THEN 'Larry'
         WHEN position = 'mgmt'  THEN 'Moe'
                                 ELSE 'Shemp' END
     , salary DESC

Can you figure this one out?