Problem solve Get help with specific problems with your technologies, process and projects.

SELECTS with specific sort orders

How do I perform a select where I have a specific sort order (order by). For example, I need my_column to be sorted by (C,E,Q,P,L).

This is best illustrated with an example. Suppose you have a table, Tasks, which has columns for a task's name and its associated priority: high, medium, or low.

create table Tasks
( TaskName varchar(100),
  Priority varchar(6)
    check( Priority in ('HIGH','MEDIUM','LOW'))
You have five tasks in your table.
insert into Tasks values ('Milk Cows','HIGH');
insert into Tasks values ('Fix Barn Door','LOW');
insert into Tasks values ('Feed Pigs','MEDIUM');
insert into Tasks values ('Gather Eggs','LOW');
insert into Tasks values ('Water Crops','HIGH');
If you were to select all tasks by priority, you would get a result set like this:
select * from Tasks
  order by Priority;

TaskName       Priority
-----------    --------
Milk Cows      HIGH
Water Crops    HIGH
Fix Barn Door  LOW
Gather Eggs    LOW
Feed Pigs      MEDIUM
But this is not the ordering we want. We would like to see high priority tasks first, then those that are medium, and finally the low priority tasks. What we have is medium priority tasks sorting after low priority tasks because alphabetically, the letter "L" comes before "M."

What we need is some way to convert the priority values into other values that, when sorted, give us the ordering we want. There are a couple of ways to do this. One way involves using the CASE statement to decode the text of the priority (HIGH, MEDIUM, and LOW) into ordered values (1, 2, and 3).

select * from Tasks
  order by ( case Priority when 'HIGH' then 1 
                           when 'MEDIUM' then 2 
                           when 'LOW' then 3 end )

TaskName       Priority
-----------    --------
Milk Cows      HIGH
Water Crops    HIGH
Feed Pigs      MEDIUM
Gather Eggs    LOW
Fix Barn Door  LOW
Note the same result could have been obtained if we had used another ordered set of values for the results of the CASE statement, such as 5, 49, and 920, or "A," "B," and "C."

Another way to get the same result involves using a lookup table. This allows us to change the ordering of queries dynamically by simply changing the values in a table.

create table PrioritySort
( Priority varchar(6),
  SortOrder numeric(1)

insert into PrioritySort values ('HIGH',1);
insert into PrioritySort values ('MEDIUM',2);
insert into PrioritySort values ('LOW',3);
Then we join the two tables together, ordering by the value in the SortOrder column.
select Tasks.* 
  from Tasks left join PrioritySort
    on Tasks.Priority = PrioritySort.Priority
  order by PrioritySort.SortOrder;

For More Information

  • What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.