View Single Post
  #8 (permalink)  
Old November 1st, 2005, 09:20 PM
Jeff Mason Jeff Mason is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by robprell
It's the group by and agregations that require a temp table when trying to Union.
Well, you still don't need a temp table for that - you can materialize a table anytime you want - the result of a SELECT statement is, after all, a table. Just give it a name and use it anywhere a "real" table would be used. Consider:
Code:
SELECT DerivedTable.Col1, MAX(DerivedTable.col2) FROM
    (SELECT col1, col2, col3, ...
        FROM <sometable>
        WHERE ...
     UNION
        SELECT colA, colB, colC, ...
        FROM <someothertable>
        WHERE ...) as DerivedTable
GROUP BY DerivedTable.Col1
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote