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

Question

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?

Answer 1

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

Answer 2

Outer query might change the order of results provided vy theinner query. Hence you will need theorder by on outer query as well. However conaidering the example query provided by you using order by ininner query is not needed.

Answer 3

You dont need to repeat order by in both queries. You should use Order By in the outer query. Like the following:

Select * From (Select   t1.* From  t1) t2 
Order By t2.date

Answer 4

In SQL Server, you will get an error when trying to use order by without top, offset, or for xml unless it is in the outermost query.

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

You can semantically get around that error by using select top 100 percent, but the order by will not be strictly enforced when that statement is called inside another statement.