Requires Free Membership to View
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 MEDIUMBut 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.
This was first published in February 2002
Join the conversationComment
Share
Comments
Results
Contribute to the conversation