views:

10073

answers:

7

How can one programmatically sort a union query when pulling data from two tables? For example,

SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY table2.field1

Throws an exception

Note: this is be attempted on MS Access Jet database engine

+1  A: 

The second table cannot include the table name in the ORDER BY clause.

So...

SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY field1

Does not throw an exception

Curtis Inderwiesche
What a good question this was. Can you tell whether your version, or the nested one, return the desired results? Or, do they both return the same results? If so, would the (other guy's) nested solution be more performant because it only does ORDER BY once?
DOK
I'm not sure the performance benefit on the Jet engine, but I would say the readability is increased due to the nesting.
Curtis Inderwiesche
+14  A: 
SELECT field1
FROM ( SELECT field1 FROM table1
       UNION
       SELECT field1 FROM table2
     )
ORDER BY field1
Anne Porosoff
This technically doesn't accomplish what you were logically asking in the original question.
Ian Boyd
+3  A: 

(SELECT table1.field1 FROM table1 UNION SELECT table2.field1 FROM table2) ORDER BY field1

Work? Remember think sets. Get the set you want using a union and then perform your operations on it.

Nick
You can also use ordinal values in your order by clause in case the fields you wish to sort on are named differently
Anson Smith
+2  A: 

Here's an example from Northwind 2007:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], [Quantity]
FROM [Product Orders]
UNION SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity]
FROM [Product Purchases]
ORDER BY [Order Date] DESC;

The ORDER BY clause just needs to be the last statement, after you've done all your unioning. You can union several sets together, then put an ORDER BY clause after the last set.

Todd Price
+10  A: 

I think this does a good job of explaining.

The following is a UNION query that uses an ORDER BY clause:

select supplier_id, supplier_name
from suppliers
where supplier_id > 2000
UNION
select company_id, company_name
from companies
where company_id > 1000
ORDER BY 2;

Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".

The supplier_name / company_name fields are in position #2 in the result set.

Taken from here: http://www.techonthenet.com/sql/union.php

Anson Smith
+1  A: 
SELECT table1Column1 as col1,table1Column2 as col2
    FROM table1
UNION
(    SELECT table2Column1 as col1, table1Column2 as col2
         FROM table2
)
ORDER BY col1 ASC
JohnMcG
+1  A: 

Sometimes you need to have the ORDER BY in each of the sections that need to be combined with UNION.

In this case

SELECT * FROM ( SELECT table1.field1 FROM table1 ORDER BY table1.field1 ) DUMMY_ALIAS1

UNION ALL

SELECT * FROM ( SELECT table2.field1 FROM table2 ORDER BY table2.field1 ) DUMMY_ALIAS2