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.
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;
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
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