ORDER BY with a UNION of disparate datasets (T-SQL)

Question

I have a query that `UNION`'s 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 problem is, I need to `ORDER` the resulting data using those columns that only exist in one or the other set, to get the data in a friendly format for the software-side. For example: Table1 has fields `ID, Cat, Price`. Table2 has fields `ID, Name, Abbrv`. The `ID` field is common between the two tables. 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…

Accepted Answer

```
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.

ORDER BY with a UNION of disparate datasets (T-SQL)

Solution

Problem