• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: SQL Facts Of UNION And ORDER BY, Take Two

    April 17, 2017 Ted Holt

    Hey, Ted:

    In last week’s tip, you mentioned that expressions are not allowed in the ORDER BY clause of a union. You can use the union as a subquery to allow the use of an expression for the ordering. An extra layer, but it gives you the result you are looking for.

    –S. Sims

    Sims is correct. I was so focused on the fact that the ORDER BY of a union does not allow expressions that I completely forgot about a workaround. His (her?) technique is probably the most common way to deal with this limitation. I’ve seen numerous examples of this technique on the Web for all sorts of database management systems. I wish I had thought to include it in my article.

    Here is astute reader Sims’ correction to my code:

    select * from (
       select custnbr, name, city, state, zip
         from cust
        union all
       select vendorid, name, city, state, zipcode
         from vendor
       ) as bb
     order by case when state = ‘MN’ then 0 else 1 end, state

    Putting the union into a subselect means that the ORDER BY clause applies to the outer SELECT, not the union, therefore the expression is allowed.

    CUSTNBR NAME CITY STATE ZIP
    10004 Zamyatin Diner Sioux Falls MN 57105
    10002 Zamyatin Diner Sioux Falls MN 57105
    10003 Gogol Mortuary San Jose CA 95111-3830
    10006 Pushkin Pizza Los Angeles CA 90034-1920
    10005 Curwood’s Bakery San Jose CA 95111
    10002 Chekov Automotive Chicago IL 60632-2015
    10004 Camus Pet Store Rockford IL 61109-2292
    10001 Tolstoy Donut Shoppe New Orleans LA 70116
    10005 Dostoyevsky Bank Baltimore MD 21224
    10001 Flaubert Fabrics Laredo TX 78045
    10003 Gide’s Garage Milwaukee WI 53207

     

    There is another advantage to this technique: the ORDER BY can reference columns that are not in the union! Here’s an example:

    select CustNbr, yearly from
       (select ' ' as rectype,
               char(custnbr) as custnbr,
               yearly
          from sales2015
        union all
        select 'T' as rectype,
               'Total' as CustNbr,
               sum(yearly) as yearly
          from sales2015) as x
    order by RecType, yearly desc

    In this query, I list both details and a summary row.  I want the summary row to appear last, so I create a record-type column with a blank value for details and a T for the summary. If I were to code the ORDER BY on the union, I would have to include the RecType column in order to sort on it. Putting the union into a subselect eliminates that requirement. The result set looks like this:

    CUSTNBR YEARLY
    10004 700.00
    10003 300.00
    10005 250.00
    10002 150.00
    10006 125.00
    10001 100.00
    Total 1,625.00

     

    I am very grateful to S. Sims for posting his comment on the IT Jungle website.  Not only did he fill in a hole that I wish I had not left, but even better, I needed this technique for a project I was working on, and he reminded me of it.

    RELATED STORY

    Guru: SQL Facts Of UNION And ORDER BY

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: Four Hundred Guru, IBM i, ORDER BY

    Sponsored by
    Krengeltech

    When it comes to consuming web APIs on your IBM i, your options often boil down to one of two things:

    First, you end up having to rely on a variety of open source and non-RPG solutions. This adds developer complexity, taking away time that could have been better spent invested in other projects. Of course, open source software is free, but generally comes at the cost of no professional support, which adds an element of risk in your production environment. RXS is completely professionally supported, and is complemented by a staff of trained IBM i developers who can address your nuanced development challenges, head on.

    Second, if you choose not to pursue an open-source solution, you’re often left having to shake up your current program architecture with proprietary software, external dependencies, and partial RPG implementations – many of which are sub-par compared to RPG-XML Suite’s wide range of features. RXS aims to simplify the efforts of developers with tools like code generators, useful commands, and subprocedures written in 100% RPG – no Java. Because they are entirely RPG, the RXS subprocedures are easy to add to new or existing ILE programs and architecture, helping to cut your development time. RPG-XML Suite offers powerful capabilities in an accessible, easy-to-implement format.

    With RPG-XML Suite, you can accomplish a variety of complex tasks, such as:

    • Calling REST and SOAP web services from your IBM i
    • Offering APIs from your IBM i
    • Creating JSON & XML
    • Parsing JSON & XML
    • Text manipulation, Base64 encoding/decoding, CCSID handling, hashing and encryption functions, and more.

    To try RXS for yourself, we recommend a free proof of concept, which not only gives you access to all of RPG-XML Suite’s subprocedures and utilities but also includes a tailor-made software demonstration that can be used as a starting point for your future API implementations.

    For a free proof of concept, contact us at sales@krengeltech.com, or visit our website for more information.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    S4i Systems Embraces Open Source Project Secrets Of IBM i Magic Act Revealed

    3 thoughts on “Guru: SQL Facts Of UNION And ORDER BY, Take Two”

    • Rob Berendt says:
      April 17, 2017 at 7:44 am

      Perhaps this is just an example SQL statement. But instead of grinding against the database twice to get subtotals also I would use some of the newer SQL statements such as this example against an IBM supplied file. (Paste into the latest Run SQL Scripts for color)

      — category: My Samples
      — description: Subtotals – With Grouping sets
      —
      — Sample with “Detail”, subtotal, grand total
      — The trick is the detail is really a subtotal for each row
      — If you don’t want detail, omit
      — Any columns selected which are not part of any grouping set, or aggregate (like sum)
      — The grouping set below flagged as “Detail”
      —
      — For information on output_queue_entries check out
      — http://ibm.biz/DB2foriServices
      —
      SELECT OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME, SPOOLED_FILE_NAME, sum(SIZE) as size, sum(TOTAL_PAGES) as pages, JOB_NAME,
      CREATE_TIMESTAMP
      FROM QSYS2.OUTPUT_QUEUE_ENTRIES
      group by grouping sets (
      (OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME, SPOOLED_FILE_NAME, JOB_NAME, CREATE_TIMESTAMP) — “Detail”
      ,(OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME) — subtotal
      ,() — Grand Total
      )
      order by OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME, SPOOLED_FILE_NAME, JOB_NAME, CREATE_TIMESTAMP;

      Reply
    • Birgitta Hauser says:
      April 19, 2017 at 1:48 am

      As an aside – Order By and Fetch First Row only can also be used within a sub-Select.
      Assumed there are multiple tables containing the sales from different companies and you have to consolidate them but only want to return the 3 Customers with the most sales for each company.
      In this case you can order the result depending on the aggregated sales in descending order and return only 3 customers.

      Example:
      Select *
      from ((
      Select 1, CustNo, sum(Amount) Total
      from Company1
      Group By CustNo
      Order By Total Desc
      Fetch First 3 Rows Only)
      Union All
      (Select 2, CustNo, sum(Amount) Total
      from Company2
      Group By CustNo
      Order By Total Desc
      Fetch First 3 Rows Only)) y
      Order By Total Desc;

      Birgitta

      Reply
    • S. Sims says:
      April 19, 2017 at 10:58 pm

      Ted did give a good union example. This It is off the subject, but to build on Ted’s and Rob’s examples and have some fun, we can throw is a “coalesce” in the first column and a “case” in the “order by” and get Ted the same results without union. There always seems to also be a few ways of doing things.

      select coalesce(char(CustNbr),’Total’) as CustNbrs
      sum(yearly) as Yearly
      from sales2015
      group by grouping sets ((CustNbr),())
      order by case when CustNbr is null then 1 else 0 end, sum(yearly) desc

      Reply

    Leave a Reply Cancel reply

TFH Volume: 27 Issue: 25

This Issue Sponsored By

  • Maxava
  • ASNA
  • WorksRight Software
  • Linoma Software
  • Manta Technologies

Table of Contents

  • IBM Gives AIX Some Of The Integration Spice Of IBM i
  • Secrets Of IBM i Magic Act Revealed
  • Guru: SQL Facts Of UNION And ORDER BY, Take Two
  • S4i Systems Embraces Open Source Project
  • Taking A New Look At Used IBM i Gear

Content archive

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

Recent Posts

  • Guild Mortgage Takes The 20-Year Option For Modernization
  • IBM i Licensing, Part 3: Can The Hardware Bundle Be Cheaper Than A Smartphone?
  • Guru: The Finer Points of Exit Points
  • Big Blue Tweaks IBM i Pricing Ahead Of Subscription Model
  • We Still Want IBM i On The Impending Power E1050
  • DRV Brings More Automation to IBM i Message Monitoring
  • Managed Cloud Saves Money By Cutting System And People Overprovisioning
  • Multiple Security Vulnerabilities Patched on IBM i
  • Four Hundred Monitor, June 22
  • IBM i PTF Guide, Volume 24, 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 © 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.