Ask The Oracle Expert: Questions & Answers

Using CASE in the SQL ORDER BY clause

Using CASE in the SQL ORDER BY clause

By  Rudy Limeback, SQL Consultant, r937.com

SearchOracle.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.