To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
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?
|
