TSQL ORDER-BY with a UNION of disparate datasets

Question

I have a query that UNIONs two somewhat similar datasets, but they both have some columns that are not present in the other -- i.e. the columns have NULL values in the resulting UNION. The purpose of the UNION is to get the data in a friendly format for the software-side.

The problem is, I need to ORDER the resulting data using those columns that only exist in one or the other set.

For example: Table1 has fields ID, Cat, Price. Table2 has fields ID (same as in Table1), Name, Abbrv.

My query looks like something like this:

SELECT t1.ID, t1.Cat, t1.Price, NULL as Name, NULL as Abbrv FROM t1
UNION
SELECT t2.ID, NULL as Cat, NULL as Price, t2.Name, t2.Abbrv FROM t2
ORDER BY Price DESC, Abbrv ASC


The ORDER BY is where I'm stuck. The data looks like this:
100---Balls-----1.53----------------------
200---Bubbles---1.24----------------------
100---------------------RedBall----101RB--
100---------------------BlueBall---102BB--
200---------------------RedWand----201RW--
200---------------------BlueWand---202BW--


But I want it to look like this:
100---Balls-----1.53----------------------
100---------------------RedBall----101RB--
100---------------------BlueBall---102BB--
200---Bubbles---1.24----------------------
200---------------------RedWand----201RW--
200---------------------BlueWand---202BW--

(apologies if those are hard to read--didn't know how else to show a table)

Keep in mind this is a very dumbed-down example and the proverbial "use a JOIN!" answer is not applicable (i.e. I already know how to JOIN them but that's not what I want in the end-result).

It could very well be that the only way to achieve this is by manipulating the data in software once it comes back from the data-layer--if that's the case then so be it. But I'm hoping it can be done in TSQL.

Answer 1

A quick solution would be to do 2 inserts into a temp table or a table variable and as part of insert into the temp table you can set a flag column to help with sorting and then order by that flag column.

Answer 2

Off the top of my head i would say the worst case scenario is you create a temporary table with all the fields do an INSERT INTO the temp table from both T1 & T2 then SELECT from the temp table with an order by.

ie. Create a temp table (eg. #temp) with fields Id, Cat, Price, Name, Abbrv, and then:

SELECT Id, Cat, Price, null, null INTO #temp FROM T1
SELECT Id, null, null, Name, Abbrv INTO #temp FROM T2
SELECT * FROM #temp ORDER BY Id, Price DESC, Abbrv ASC

NB: I'm not 100% sure on the null syntax from the inserts but i think it will work.

EDIT: Added ordering by Price & Abbrv after id... if Id doesn't link T1 & T2 then what does?

Answer 3

Select ID, Cat, Price, Name, Abbrv
From
(SELECT t1.ID, t1.Cat, t1.Price, t1.Price AS SortPrice, NULL as Name, NULL as Abbrv 
FROM t1
UNION
SELECT t2.ID, NULL as Cat, NULL as Price, t1.Price as SortPrice, t2.Name, t2.Abbrv 
   FROM t2
   inner join t1 on t2.id = t1.id
) t3
ORDER BY SortPrice DESC, Abbrv ASC

Somehow you have to know the data in table 2 are linked to table 1 and share the price. Since the Null in abbrv will come first, there is no need to create a SortAbbrv column.

Answer 4

You should use UNION ALL instead of UNION to save the cost of duplicate checking.

SELECT *
FROM
(
SELECT t1.ID, t1.Cat, t1.Price, NULL as Name, NULL as Abbrv FROM t1
UNION ALL
SELECT t2.ID, NULL as Cat, NULL as Price, t2.Name, t2.Abbrv FROM t2
) as sub
ORDER BY
  ID,
  CASE WHEN Price is not null THEN 1 ELSE 2 END,
  Price DESC,
  CASE WHEN Abbrv is not null THEN 1 ELSE 2 END,
  Abbrv ASC