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?

–William

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.

–Ted