sqlsql-order-byinner-query

SQL - Do you have to repeat ORDER BY in outer query?

I have a query:

select   t1.*
from     t1
order by t1.date

This query in enclosed in another query:

select * from (select   t1.*
               from     t1
               order by t1.date) t2

Do you have to repeat the ORDER BY in the outer query? Like this:

select * from (select   t1.*
               from     t1
               order by t1.date) t2 order by t2.date

Does the answer change if the inner query is moved to a CTE?

Solution

  • yes ,you have to repeat order by in outer query,if you want the output to be ordered.

    SQLserver honours order by in outer query only and your inner order by is meaningless