You can't use two different order by
clauses when joining tables with a union
. (See this Stackoverflow) So why does it work when I use a CTE?
;with x as (
select top 1 * from myTable order by col1
union select top 1 * from myTable order by col2
)
select *
from x
The query within the CTE does not work standalone.
Based on Stu's comment:
Order by
sorts an entire query. In a CTE, you can't sort the inner query, but you can produce a top
. If you use an order by
with top
, it will service the top
but will not sort the results. For example, I modified my query to return top 5
and can see that the results are the correct data but are not sorted.