Q

More about ORDER BY for a specified sequence

How could I sort a query result according to a specific order (not ascending or descending) that I will be supplying from the values of the column to be sorted?

How could I sort a query result according to a specific order (not ascending or descending) that I will be supplying

from the values of the column to be sorted?

One technique for this was described in detail in a previous answer, ORDER BY a specified sequence (21 April 2006):

order 
    by case when projectid = 3 then 'Curly'
            when projectid = 1 then 'Larry'
            when projectid = 2 then 'Moe'
          else null end 

In summary, each row of the intermediate result set has an additional column appended to it, consisting of the result of the CASE expression. This additional column is not returned (because it isn't in the SELECT list, although it could be, if so desired, which you might do during testing). These values are chosen carefully (if not, as in the above example, capriciously), so that they achieve the desired sequencing.

Here's another method. Suppose we have data containing only the three-character month names Jan, Feb, Mar, Apr.

create table mths
( mth char(3) not null primary key 
);
insert into mths
values ('Apr'),('Feb'),('Jan'),('Mar')
;

Now we want to sort these names into their natural calendric sequence.

select mth
     , case when mth='Jan' then 0 else 1 end as m1
     , case when mth='Feb' then 0 else 1 end as m2
     , case when mth='Mar' then 0 else 1 end as m3
     , case when mth='Apr' then 0 else 1 end as m4
  from mths
order 
    by case when mth='Jan' then 0 else 1 end
     , case when mth='Feb' then 0 else 1 end
     , case when mth='Mar' then 0 else 1 end
     , case when mth='Apr' then 0 else 1 end

Ordinarily, you would not include the ORDER BY columns in the SELECT like that. They are included here so that you can see how they work. The result of the above query is:

mth m1 m2 m3 m4
Jan  0  1  1  1
Feb  1  0  1  1
Mar  1  1  0  1
Apr  1  1  1  0

As mentioned, those four "extra" columns would normally not be included in the SELECT clause, they would just be in the ORDER BY. Can you see how they work?

Here is a third solution:

select mth
     , position(mth in 'JanFebMarApr') as p
  from mths
order 
    by position(mth in 'JanFebMarApr')

This query produces:

mth  p
Jan  1
Feb  4
Mar  7
Apr 10

Neat, eh?

This was first published in March 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close