Select a,b,c 
from table

Select c,b,a
from table

select SalesOrderID,
       CustomerID,
       OrderDate,
       ROW_NUMBER() over (Partition By CustomerId order by OrderDate asc) as RownAsc,
       ROW_NUMBER() over (Partition By CustomerId order by OrderDate Desc) as RownDesc  
from sales.SalesOrderHeader
order by CustomerID,OrderDate
option(maxdop 1)

If you look at the execution plan, you will see similar to this

That is three sorts.  One for RownAsc,  one for RownDesc and the final one for the ‘Order by’ clause.  Sorting is an expensive operation and one that should be avoided if possible.  So with this in mind, it may come as some surprise that the optimizer does not re-order operations to group them together when the incoming data is in a similar (if not exactly the same) sorted sequence. 

A simple change to swap the RownAsc and RownDesc columns to produce this statement :

select SalesOrderID,
       CustomerID,
       OrderDate,
       ROW_NUMBER() over (Partition By CustomerId order by OrderDate Desc) as RownDesc  ,
       ROW_NUMBER() over (Partition By CustomerId order by OrderDate asc) as RownAsc
from Sales.SalesOrderHeader
order by CustomerID,OrderDate
option(maxdop 1)

Will result a different and more efficient query plan with one less sort.

The optimizer, although unable to automatically re-order operations, HAS taken advantage of the data ordering if it is as required.  This is well worth taking advantage of if you have different sorting requirements in one statement. Try grouping the functions that require the same order together and save yourself a few extra sorts.