Brian Tkatch wrote:
The actual case is a UNION query from two different TABLEs, so that is
not possible.
Sorry for coming late to the party....
ORDER BY in SQL is attached to SELECT.
UNION (ALL) is not a SELECT.
Ironically we had to actively "disable" ORDER BY on UNION to adhere to
these rules. DB2 was originally happily eating it.
The solution of pushing the UNION into a derived table has been
provided, so no harm is done.
I agree with Knut that the decision to standardize ORDER BY with
constant numbers to mean column positions was a bad idea. It originated
from the limitatiosn of products at the time to match the expression in
teh select list with an an identical expressions in the ORDER BY clause.
Using correlation names from the select list in the order by clause
would have been a much cleaner solution.. too late.
Obviosuly ORDER BY in to sort the result set of a cursor is a good idea.
Also obviosuly ORDER BY in an OLAP expression (which may benested inside
a query) is a useful thing.
So what about a nested ORDER BY?
Well Knut as brought up one important point: Orthogonality.
E.g. if a tool like "Query Patroller" needs to take a user submitted
query and store it away for later retrieval of teh result set having an
orthoginal language is beneficial. So for reasons of orthogonality alone
ORDER BY in subquery was deemed necessary.
But there is another usage:
Combination of ORDER BY with FETCH FIRST (aka. TOP/LIMIT).
What this gives us is a "truncating" select operator. Truncating based
on an ORDER is nothing bad.
The trick is that the ORDER does NOT leak. The derived table is NOT ordered.
There is abolsutley nothing wrong with that from a relational point of view.
Arguing that such things are evil is like arguing that any other scalar
operation thnt PLUS and MINUS are evil. There are logical extensions
with new operators which one can make without breaking the model: MULT,
DIV, MOD, .... same with relational.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab