sorting subqueries with UNION

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jesper - 30 Nov 2006 14:46 GMT

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

Reply to this Message

Allen Browne - 30 Nov 2006 15:09 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> When joining SELECTs with
>
[quoted text clipped - 16 lines]
>
> / Jesper

Reply to this Message

John Spencer - 30 Nov 2006 17:06 GMT

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.

> No. The values are all combined into one long list in a UNION.
>
[quoted text clipped - 23 lines]
>>
>> / Jesper

Reply to this Message

Marshall Barton - 30 Nov 2006 17:22 GMT

>When joining SELECTs with
>
[quoted text clipped - 11 lines]
>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

Signature

Marsh
MVP [MS Access]

Reply to this Message