Problem solve Get help with specific problems with your technologies, process and projects.

CASE expressions in the ORDER BY clause

SQL expert Rudy Limeback discusses using a CASE expression in the ORDER BY clause.

The other day I was going through some tutorials about using the CASE expressions used in the ORDER BY clause of a SELECT statement. Can you please explain it through some easy examples for a better understanding?

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
    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
    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?

Dig Deeper on Oracle development languages

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.