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.

5 comments
wclardy
wclardy

Here's a slightly cleaner work-around using CASE logic: --=============================== SELECT CASE ISNULL( Genre , '' ) WHEN '' THEN Category ELSE ' ' + Genre END AS Details , Item_Count FROM ( SELECT DISTINCT Category AS Category , NULL AS Genre , NULL AS Item_Count FROM Items UNION SELECT Category , Genre , COUNT( * ) AS Item_Count FROM Items GROUP BY Category , Genre ) AS demo ORDER BY Category , ISNULL( Genre , '' ) ; --===============================

Tony Hopkinson
Tony Hopkinson

SELECT 'BOOKS' AS Genre, '' should be Null cast as int, as if you pull this resultset in ItemCount will be a varchar. Or you could make it the last line and not need the cast, geiven you replace '' with a null. Presumably this is a one off as this is an inflatable dartboard solution Denormalising with a Genres table with a displayorder column, and replacing Count(*) with Count(Category) , would make it a simple join and aggregate and cope with new categories, and reordering without having to find an rewrite the sql.

Justin James
Justin James

Yup, this was a one-time report. In addition, I can't monkey with it anyways, because I'm pulling from Microsoft CRM's tables, and it wasn't worth making a View over... Also, didn't care about the types, since I was doing a copy/paste from the SQL Management Studio results window straight into Excel. :) J.Ja

Tony Hopkinson
Tony Hopkinson

you would have figured it out, or casted it and coped with the empty string. :p Saying that you'd have needed to use is IsNull, even with my quibble on board. I do a fair bit of this sort of stuff becasue an immovable leagcy database, so I've got in the habit of forcing the logical datatype.