Guru: SQL Facts Of UNION And ORDER BY, Take Two
April 17, 2017 Ted Holt
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.
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.
|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|
|10004||Camus Pet Store||Rockford||IL||61109-2292|
|10001||Tolstoy Donut Shoppe||New Orleans||LA||70116|
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:
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.