What is the execution order of the PARTITION BY clause compared to other SQL clauses?

Question

I cannot find any source mentioning execution order for Partition By window functions in SQL.

Is it in the same order as Group By?

For example table like:

Select *, row_number() over (Partition by Name) 
from NPtable 
Where Name = 'Peter'

I understand if Where gets executed first, it will only look at Name = 'Peter', then execute window function that just aggregates this particular person instead of entire table aggregation, which is much more efficient.

But when the query is:

Select top 1 *, row_number() over (Partition by Name order by Date) 
from NPtable 
Where Date > '2018-01-02 00:00:00'

Doesn't the window function need to be executed against the entire table first then applies the Date> condition otherwise the result is wrong?

Answer 1

Window functions are executed/calculated at the same stage as SELECT, stage 5 in your table. In other words, window functions are applied to all rows that are "visible" in the SELECT stage.

In your second example

Select top 1 *, 
row_number() over (Partition by Name order by Date) 
from NPtable 
Where Date > '2018-01-02 00:00:00'

WHERE is logically applied before Partition by Name of the row_number() function.

Note, that this is logical order of processing the query, not necessarily how the engine physically processes the data.

If query optimiser decides that it is cheaper to scan the whole table and later discard dates according to the WHERE filter, it can do it. But, any kind of these transformations must be performed in such a way that the final result is consistent with the order of the logical steps outlined in the table you showed.