As a follow-up on that question:
If I have constructed a query containing a WINDOW expression, how can I filter the result set based on the expression?
The order of execution with a WINDOW expression is as following (taken from the docs): the order of evaluation of the clauses within a SQL statement is:
Well, usually one would filter with the help of WHERE or HAVING but as the window expression is executed lateron, these clauses won't work.
So, relating to the example from my previous question:
How can I filter only those rows with QuantityDecrease >= 20 when a WHERE or HAVING clause do not apply here?
The answer is easier than I thought (and that's a reason I like to share my learning experience):
One can use the query as a derived table and then filter using a simple WHERE clause, such as
This returns the wanted results:
I'm starting to get comfortable with window expressions. (Yes, I do know there's much more on this.)
answered 01 Sep '10, 16:49