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
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.