ORDER BY caluse WITHIN a Union query

[Guest]

I want to order to results of just one SELECT statement within a larger UNION
query:

SELECT first_line FROM first_query

UNION ALL (SELECT second_group_of_lines FROM second_query ORDER BY
second_group_of_lines DESC)

UNION ALL SELECT third_line FROM third_query;

The idea is to create lines of text for a report. The second subquery
returns a few rows of data, which I want sorted within themselves, however
the first and third should remain separate. The result would be similar to:

6 meetings occured on Tuesday:
3 in the Boardroom
2 in the Auditorium
1 in the basement
These meetings lasted a total of 7 hours.

The detailed list of meetings (the three rows in the centre) needs to be
sorted, descending.

The above code returns no errors: the ORDER BY clause is simply ignored
completely.

Any ideas?

 

[Guest]

You can not sort in a union query but there is a way to accomplish it for
your report. Include a field to be used in Grouping and Sorting in the
report. In the SELECT statement add a field like 1 AS SortGroup for the
set you want to remain as is. In the SELECT statement add a field
second_group_of_lines AS SortGroup for the set you want to sort DESC. Then
in the report sort descending on SortGroup.

 

[John Spencer]

UNION queries can only be sorted after the union. (and sort uses the
fieldnames or alias of the first query as field (column) names).

Try something like the following.

SELECT first_line as TheLine, "A" as SortOrder
FROM first_query
UNION ALL
SELECT second_group_of_lines, "B" as SortOrder
FROM second_query
UNION ALL
SELECT third_line FROM third_query, "C" as SortOrder
ORDER BY SortOrder, TheLine

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Just a memo warning: If you include a memo field
in the select statements, the current version
of JET can try to sort on the first 255 characters
of the memo field.

BUT you shouldn't do that in a Union query. Sorting
on a memo field has a few bugs, and is only reliable
in simple queries. A Union query will sometimes return
trash if you try to sort on a memo field.

(david)