• 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
    Midrange Dynamics North America

    Want to deliver DevOps on IBM i?

    DevOps enables your IBM i development teams to shorten the software development lifecycle while delivering features, fixes, and frequent updates that are closely aligned with business objectives. Flexible configuration options within MDChange make it easy to adapt to new workflow strategies and policies as you adopt DevOps practices across your organization.

    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

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

  • The Power11 Transistor Count Discrepancies Explained – Sort Of
  • Is Your IBM i HA/DR Actually Tested – Or Just Installed?
  • Big Blue Delivers IBM i Customer Requests In ACS Update
  • New DbToo SDK Hooks RPG And Db2 For i To External Services
  • IBM i PTF Guide, Volume 27, Number 33
  • Tool Aims To Streamline Git Integration For Old School IBM i Devs
  • IBM To Add Full System Replication And FlashCopy To PowerHA
  • Guru: Decoding Base64 ASCII
  • The Price Tweaking Continues For Power Systems
  • IBM i PTF Guide, Volume 27, Numbers 31 And 32

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