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   1.24                       
200                     RedWand    201RW   
200                     BlueWand   202BW   

I'm hoping this can be done in T-SQL.

Set

Do the id's not link your T2 products to the T1 product? If not how do you know that the RedWand & BlueWand come straight after Bubbles? – Purington

He says in the question: "Table2 has fields ID (same as in Table1)" – Reflect

Thanks for all the answers everybody; this is just my first question, but this already seems like a great community. Also, for those that would criticize the 'architecture' that my question implies--please note what I said above and below in the accepted answer: this is dumbed-down example! The 'tables' in my question actually represent two very complex SELECT queries that involve multiple tables and subqueries, with aggregates & calculated fields. Thanks again! – Set

Answer 1

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.

Ogg

Running this query produces a syntax error unless the subquery has an alias. I made the correction for you. +1 – Mcghee

Right, so essentially this is just putting the Price into the 2nd query so that it can be sorted with. This would be a great solution if the actual query wasn't so complicated already--it has subqueries and aggregate & calculated fields, one of which is the primary SORT-er (i.e. Price in this case). What I'd actually have to do is duplicate the entire 1st query again for the JOIN in the lower part of the UNION. So +1 and accepted, but I'm not going to waste any more time trying to get the real thing working like this; it's far easier to manipulate the data in code, and probably cleaner. – Set

Answer 2

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

Reflect

The real query I'm working does have UNION ALL. Once again, ID is not the primary ORDER-er, Price is. The IDs can't be used for sorting in the 'real' query that I'm basing this on. – Set

Answer 3

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.

Huntingdonshire

I did think of that, but trying to avoid it. Good answer though. – Set

Answer 4

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?

Purington

Ignore the ID; Price is the primary ORDER-er. – Set

Answer 5

If you have a limited number of results expected from your query results you can use OFFSET / FETCH Keywords. Here assumption is , result set will not exceed 10000 rows.

This SQL Statement will order table1 results internally. Then it will order table2 results internally. And it will merge these subsets in this order.

For example :

select name  from table1 order by columnNameX OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY
union all 
select name   from table2 order by columnNameY OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY

Osteoma