• 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
    Maxava

    Migrate IBM i with Confidence

    Tired of costly and risky migrations? Maxava Migrate Live minimizes disruption with seamless transitions.

    Upgrading to Power10, Power11, or cloud hosted system, Maxava has you covered!

    Book A Consultation Today

    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

Volume 4, Number 33 -- October 6, 2004
THIS ISSUE
SPONSORED BY:

iTera
COMMON
WorksRight Software

Table of Contents

  • A Generic Command Choice Validity Checker
  • Conditional Sorting with SQL
  • Admin Alert: Make User Profiles Go Cross-Partition with OpsNav

Content archive

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

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • 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

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