Conditional Sorting with SQL
October 6, 2004 Hey, Ted
We are a small business in Ohio. Most of the people we do business with are also in Ohio. Is there a way we can retrieve a list of clients with the Ohio clients listed first, followed by all others, in order by state?
SQL/400 can handle this task for you. Use a CASE structure in the ORDER BY clause.
select custnbr, custname, ccity, cstate from customer order by case cstate when 'OH' then 0 else 1 end, cstate, ccity
The first sort field is a case expression. For records that have a state value of OH, this sort field is zero. For all others, this sort field is one. Ohio records sort ahead of all others.
Since you want other states in alphabetical order, I placed the CSTATE field as the second sort field. I didn’t know your sort preference within state, so for the third sort field I chose CCITY. The system produces a record set like this one.
Customer account Customer number name City State 3 Army Surplus Brunswick OH 30003 Snowman Heat & Air Lexington OH 56 Jak's Liver Emporium Mexico OH 1 48% of Nothing Klondike AZ 222 Sardine Paradise Happah Palloolah CA 2 Robert R. Roberts IV New Yolk CA 3 Donaldson Electric Whittier CA 44 Joe's Shoes Duluthe GA 3 Sal Monella Dienstag MO 10001 Sue's Bridle Shop Saltillo MS 111 Ames Wholesale Tupelo MS 40004 Grayson Paul Little City ND 345 Gretta's Gifts Saddle Brook NJ 44 Ben Dover Jaxon Whole NV 777 Pretty Boy's Gym Lost Angeles NY 1 Xolomon Solutions Tulsa OK 20002 Bank of Steele Medina PA
This works under V5R2, but I can’t vouch for earlier releases.