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?

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top