Data Management

Ordering in UNIONed SQL results

Developer Justin James passes along a quick SQL coding trick for getting the query results to look just right.

Not long ago, I found myself writing some SQL code for the first time in a while. I had a table that looked roughly like this:

Category Genre Item Price
Books Fiction The Trial 4.95
Movies Sci-Fi Blade Runner 6.95
Books Fiction Night Watch 10.95
Books Fiction Snowcrash 7.99
CDs Progressive Ammonia Avenue 11.97
CDs Rock Face Value 12.99
Movies Horror The Ring 14.95
CDs Rap The Message 9.95
Books Non-Fiction Working XI to V 5.95

What I wanted was to get results that looked like this:

Details Count
BOOKS
Fiction 3
Non-Fiction 1
CDS
Progressive 1
Rap 1
Rock 1
MOVIES
Horror 1
Sci-Fi 1

Up front, it wasn't too difficult, I just used a query like this:

SELECT 'BOOKS', '' UNION

SELECT Genre, Count(*) FROM Items WHERE Category = 'Books' GROUP BY Genre

SELECT 'CDS', '' UNION

SELECT Genre, Count(*) FROM Items WHERE Category = 'CDs' GROUP BY Genre

SELECT 'MOVIES', '' UNION

SELECT Genre, Count(*) FROM Items WHERE Category = 'Movies' GROUP BY Genre

Everything went well, but when I ran the query, my results were in alphabetical order. Apparently, SQL Server decides to apply an ordering post-SELECT. It is allowed to do that, since SQL results are non-deterministic with ordering without an ORDER BY clause. To keep the intended order of things, I used the following workaround code:

SELECT * FROM

(SELECT 'BOOKS' AS Genre, '' AS ItemCount, 1 AS ItemOrder UNION

SELECT Genre, Count(*), 2 FROM Items WHERE Category = 'Books' GROUP BY Genre

SELECT 'CDS', '', 3 UNION

SELECT Genre, Count(*), 4 FROM Items WHERE Category = 'CDs' GROUP BY Genre

SELECT 'MOVIES', '', 5 UNION

SELECT Genre, Count(*), 6 FROM Items WHERE Category = 'Movies' GROUP BY Genre) ItemCounts

ORDER BY ItemOrder

The "BOOKS" header select needs the "AS" clauses so that the resulting query has named columns, which is needed for the ORDER BY. With this trick, we can group the results into the appropriate places as needed.

J.Ja

About

Justin James is the Lead Architect for Conigent.

Editor's Picks