Search code examples
sql-serversql-order-byunioncommon-table-expression

Why does this work? Two different order bys with union in CTE


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.


Solution

  • 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.