I like your version too Art. It's clever. It's not a workaround though since there's no bug. After all, ORDER BY operates on the result of a SELECT query. A query like— SELECT x FROM T UNION SELECT y FROM U; —is only one query. It produces only one table (which is why the number and type of the columns in the two projections must exactly match), and it can only be ORDERed BY once, as a whole. Putting parenthesis around one of the SELECT expressions doesn't change that. This is still one query (there are no subqueries), and all the parentheses are completely redundant (though valid): SELECT x FROM T UNION (SELECT y FROM U) UNION ((SELECT z FROM V)); To have multiple ORDERings, we need multiple queries, producing separate tables (which can then be combined). Exactly as you have done with— SELECT x FROM (SELECT... ORDER BY...) WHERE x IN (SELECT... ORDER BY...) AND x IN (SELECT... ORDER BY...); This might be faster— SELECT x FROM (SELECT... ORDER BY...) WHERE x IN ( SELECT y FROM (SELECT... ORDER BY...) WHERE y IN (SELECT... ORDER BY...) ); —because it reduces the filtering comparisons to the minimum necessary (divide and conquer). There are probably faster ways again though?
|