Discussion on:

Ordering in UNIONed SQL results

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.

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

Thanks for a handy work-around.!

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 , '' ) ;
--===============================