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

Using CASE in the SQL ORDER BY clause

Read about why one Oracle user is getting an error when trying to use CASE in the SQL ORDER BY clause.

Rudy Limeback
r937.com

I am trying to use a CASE expression in the ORDER BY clause of my SQL statement, but I want to order by more than...

one column. I keep getting a "missing keyword" error when I try to compile this.

ORDER BY
CASE WHEN sortorder = 1 THEN x
WHEN sortorder = 2 THEN x,y
END;

"sortorder" is a variable I pass in, by the way.

The first thing to notice is that in both cases, for sortorder 1 and also for sortorder 2, the rows must be sorted into major sequence on the "x" column. So we can simplify the ORDER BY clause like this: 

ORDER 
    BY x
     , ... something for sortorder 2

Now the only thing to worry about is sortorder 2. This requires that the rows be in sequence by the "y" column values within each "x" value.

But what if we simply do this:

ORDER 
    BY x
     , y

Let's recap. If sortorder is 2, then the rows will be in sequence by "y" within "x," and we're all set, right? But if sortorder is 1, then the rows will also be in sequence by "y" within "x," but for sure they'll be in sequence by "x." So that's okay too, right?

You don't need a CASE expression at all.

This was last published in January 2009

Dig Deeper on Oracle and SQL

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.

Meet all of our Oracle Database / Applications experts

View all Oracle Database / Applications questions and answers

Join the conversation

2 comments

Send me notifications when other members comment.

Register

Please create a username to comment.

I am trying to use a CASE expression in the ORDER BY clause of my Oracle SQL statement, but I want to order by more than one column. I keep getting a "missing keyword" error when I try to compile this.

ORDER BY
CASE
WHEN :P_SORT_BY = '1' THEN 1
WHEN :P_SORT_BY = '5' THEN 5 desc
WHEN :P_SORT_BY = null THEN 1,3,4
END;

":P_SORT_BY" is a variable I pass in, by the way.
Cancel
JWaldrip:  There are two errors in your ORDER BY clause:

1) You can't specify DESC or ASC inside the CASE expression. The type of ordering cannot be conditional.  The ASC or DESC must be specified outside the CASE, or in other words, after the END.

2) You can specify only one expression to order by inside the CASE expression, if you want to order by 1,3,4 you need to add "3,4" after the END of the CASE expression.  The case must return only one value.

For more info about the CASE statement, check this article:
CASE expressions in Oracle SQL
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close