Great, now we can get down to work. Back in Part 1, we said that you can't use window functions in the
WHERE clause. Why is that so? Because all query elements are processed in a very strict order:
FROM – the database gets the data from tables in
FROM clause and if necessary performs the
WHERE – the data are filtered with conditions specified in
GROUP BY – the data are grouped by with conditions specified in
- aggregate functions – the aggregate functions are applied to the groups created in the
GROUP BY phase.
HAVING – the groups are filtered with the given condition.
- window functions.
SELECT – the database selects the given columns.
DISTINCT – repeated values are removed.
EXCEPT – the database applies set operations.
ORDER BY – the results are sorted.
OFFSET – the first rows are skipped.
TOP – only the first rows are selected.
Practically, this order means that you can't put window functions anywhere in the
GROUP BY or
HAVING clauses. This is because at the time of calculating these elements, window functions are not yet calculated – and it's impossible to use something which is not already available.