sorting subqueries with UNION

[Jesper]

When joining SELECTs with

SELECT name FROM table1
UNION ALL
SELECT name FROM table2
UNION ALL
SELECT name FROM table3

kan I sort the individuel SELECTs differently for example as

SELECT name FROM table1 ORDER BY name ASC
UNION ALL
SELECT name FROM table2 ORDER BY name DESC
UNION ALL
SELECT name FROM table3 ORDER BY adress ASC

I doesn't seem to work for me.
Thanks.

/ Jesper

 

[Allen Browne]

No. The values are all combined into one long list in a UNION.

You can add an ORDER BY clause after the last SELECT.
It applies to all records.

Any ORDER BY clauses for the earlier SELECTs have no effect, as you found.

 

[John Spencer]

Why do you need to do this?
IF the fields you need to sort by are all the same type and you want to sort
in the same direction, you might be able to do something like the following.

SELECT Name, Name as SortBy
FROM Table1
UNION ALL
SELECT Name, Name as Sortby FROM table2
UNION ALL
SELECT name, Address as SortBy FROM table3
ORDER BY SortBy

And if you wanted to be really creative, you could use a calculated value to
do a descending sort. Numbers you could just switch the sign on, text
would be more complex. For a one letter sort, you could use something like
the UNTESTED expression
Chr(155 - Asc(UCase([Name])))
Although that could return some interesting results.

 

[Marshall Barton]

When joining SELECTs with

SELECT name FROM table1
UNION ALL
SELECT name FROM table2
UNION ALL
SELECT name FROM table3

kan I sort the individuel SELECTs differently for example as

SELECT name FROM table1 ORDER BY name ASC
UNION ALL
SELECT name FROM table2 ORDER BY name DESC
UNION ALL
SELECT name FROM table3 ORDER BY adress ASC

If you do not rellay need the ASC/DESC part, you can get the
desired effect by adding a field to identify the select:

SELECT name, 1 As grp FROM table1
UNION ALL
SELECT name, 2 As grp FROM table2
UNION ALL
SELECT name, 3 As grp FROM table3
ORDER BY grp, name

If you want to sort on a numeric value instead of a text
value, you can simulate the ACS/DESC by calculating a second
sort value:

SELECT x, 1 As grp, x As srt FROM table1
UNION ALL
SELECT x, 2 As grp, 99999-x As srt FROM table2
UNION ALL
SELECT x, 3 As grp, x As srt FROM table3
ORDER BY grp, srt

 

[Jesper]

Thanks very much all of you.
I'm using Karl's and Marshall's suggestions and it works great.
Thanks.

/ Jesper