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 being attempted on MS Access Jet database engine
Using a concrete example:
SELECT name FROM Folders ORDER BY name
UNION
SELECT name FROM Files ORDER BY name
Files:
name
=============================
RTS.exe
thiny1.etl
thing2.elt
f.txt
tcpdump_trial_license (1).zip
Folders:
name
============================
Contacts
Desktop
Downloads
Links
Favorites
My Documents
Desired Output: (results of first select first, i.e. folders first)
Contacts
Desktop
Downloads
Favorites
Links
My Documents
f.txt
RTMS.exe
tcpdump_trial_license (1).zip
thiny1.etl
thing2.elt
SQL to achieve the desired results:
SELECT name
FROM (
SELECT 1 AS rank, name FROM Folders
UNION
SELECT 2 AS rank, name FROM Files) dt
ORDER BY rank, name
By using order separately each subset gets order, but not the whole set, which is what you would want uniting two tables.
You should use something like this to have one ordered set:
SELECT TOP (100) PERCENT field1, field2, field3, field4, field5 FROM
(SELECT table1.field1, table1.field2, table1.field3, table1.field4, table1.field5 FROM table1
UNION ALL
SELECT table2.field1, table2.field2, table2.field3, table2.field4, table2.field5 FROM table2)
AS unitedTables ORDER BY field5 DESC