CirquedeSQLeil (5/7/2010) Paul White NZ (5/7/2010)
Though I got this correct, it appears there is a typo in the correct answer. My version had the last two flip-flopped based on the source document provided in the answer.
What are you referring to here Jason?
It must be hard to spot - I can't see anything wrong...
Your list has it correct, Itzik's list has it correct. The correct Answer (and explanation) has inverted the order by and top (order by then top as opposed to the document which says top then order by).
It's not my habit to disagree with Itzik, but in this case I have to make an exception.
Lemme ask you a simple question - in a query that has a TOP and an ORDER BY clause, how exactly should SQL Server process the TOP without sorting the rows first? *IF* Microsoft had decided to use a seperate clause to define the order for the TOP instead of overloading the ORDER BY clause (for instance TOP ... OVER (ORDER BY column)), then I would agree with TOP first (which includes ordering by the OVER (ORDER BY) specificion of the TOP), ORDER BY (for presentation) last. But as it stands, it simply does not make sense to do an ORDER BY after a TOP that has already included the same sort operation.
I disagree with the list and Itzik, and I agree with the order as presented in the question.
Note that, for most clauses, the logical processing order is defined by the ANSI standards. However, the TOP clause is proprietary; ANSI does not support it. And Microsoft never officially announced the logical order, so this is all speculation. Both on Itzik's and on my part.
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis