• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Rocket Software

    Two Steps Forward, No Steps Back

    For over 35 years, Rocket Software’s solutions have empowered businesses to modernize their infrastructure, unlock data value, and drive transformation – all while ensuring modernization without disruption.

    LEARN MORE

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Bug Busters Enables True Object Broadcasting with RSF 7.2 Q&A: iSeries GM Borman to Focus on i5/OS Sales

    Leave a Reply Cancel reply

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle