• 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
    Chilli IT

    Chilli is one of the UKs leading IBM support and management providers with 20 years’ experience in the power and storage industry. Our bespoke solutions for maintenance, security and infrastructure delivers a service which is cost effective, increases productivity and enhances efficiency. Our ethical approach and unrivalled knowledge has secured business partnerships with blue-chip companies in the technology, retail, banking and travel sectors.

    As an IBM Business Partner, we provide you with the peace of mind that you are working in partnership with a company accredited to the highest standard. Our team of experts have worked together for many years and deliver projects which include consolidation, High Availability, Operating System upgrades; and backup and recovery installations.

    Contact us to see how we can help your business with IBM support and management.

    www.chilli-it.co.uk

    info@chilli–it.co.uk

    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

  • How Committed Is Big Blue To The IBM Cloud?
  • Immutable Copies Are Only As Good As Your Validation
  • Guru: IBM i *USRPRF Security
  • ERP Transitions Loom for SAP on IBM i Customers
  • Inflation Pumps Up Global IT Spending, Supply Chain Deflates It
  • COMMON Set for First Annual Conference in Three Years
  • API Operations Management for Safe, Powerful, and High Performance APIs
  • What’s New in IBM i Services and Networking
  • Four Hundred Monitor, May 18
  • IBM i PTF Guide, Volume 24, Number 20

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 © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.