Ask the Expert

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?

    Requires Free Membership to View

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

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: