Top-N per group: why a subquery is needed

Question

Accepted Answer

SQL has a logical order of operations: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. Window functions live in the SELECT phase, which means WHERE has already finished by the time rn is calculated. Referencing a window function alias in WHERE on the same level causes an error. Wrapping the windowed query in a subquery or CTE promotes it to a derived table, making rn a real column that the outer WHERE can see. This is why the top-N pattern always needs two levels of query.