On 23 Jul 2004 at 9:34, Fogelson, Steve wrote: > It appears the results are in reverse order. IE: > Row (from R3) > Row (from R2) > Row (from R1) > Is this how a UNION is supposed to work?
Steve. If you don't use the keyword ALL after UNION, the SQL standard says that before the rows are returned, the database engine should remove all duplicate results from the combined result set. You may get a different answer if you instead do this: SELECT R1 AS Row FROM T1 UNION ALL SELECT R2 AS Row FROM T2 UNION ALL SELECT R3 AS Row FROM T3 Actually, according to the SQL standard, in the absence of an ORDER BY clause, there is no defined order in which rows are returned. The database engine gets to use its own optimizer to figure out the best way to return rows. With UNION, you can put an ORDER BY clause on any one of the queries, and it will apply to the complete data set, not just the subset of rows from the individual query. Since you might not be choosing columns with the same names, it may be more understandable to use the ordinal form (specifying the first, second, third, etc. item in the list after the word SELECT) of the ORDER BY clause, e.g. SELECT R1 AS Row FROM T1 UNION SELECT R2 AS Row FROM T2 UNION SELECT R3 AS Row FROM T3 ORDER BY 1 ASC Bill ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
