0

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.

Rachel
  • 686
  • 1
  • 6
  • 18
  • Side Note: This behaviour is not unique to CTEs, but any derived table. [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=41d0cdf3576e92d9b29927071ddec80a) – Thom A Feb 16 '22 at 16:59
  • 1
    The way the *order by* clause works is that it orders the *entire* result set; When you use a derived table, CTE or view, you use *order by* on the outer query. However with a *union* of two or more tables, an *order by* orders the result of the union *except* when used in a CTE etc where the *order by* is irrelevant (and not allowed) unless used to assist *top()*, where its usage is allowed as in your example, where the second *order by* operates on that statement alone and not on the entire union. – Stu Feb 16 '22 at 17:12
  • Thanks, that's very interesting. Is there any documentation around this? – Rachel Feb 16 '22 at 17:25
  • 1
    Your query doesn't really "work" either. ORDER by clauses satisfy the TOP usage but they DO NOT order the resultset of your query despite a pattern you only think you see. If the order of rows in the resultset matter, then the outermost query must have an ORDER BY clause. Lastly be extremely cautious about using 10 year old threads as a reference. And just because something is upvoted does not make it a good question or answer. – SMor Feb 16 '22 at 18:03

1 Answers1

0

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.

Rachel
  • 686
  • 1
  • 6
  • 18