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

This was last published in July 2008

Dig Deeper on Oracle development languages

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close