SAP SQL Anywhere

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:

FROM -> WHERE -> GROUP BY -> HAVING -> WINDOW -> DISTINCT -> ORDER BY

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:

select ProductID, ProductDay, Quantity,
   sum(Quantity) over (partition by ProductID order by ProductDay
      rows between current row and current row) as CurrentQuantity,
   sum(Quantity) over (partition by ProductID order by ProductDay
      rows between 1 preceding and 1 preceding) as PreviousQuantity,
   PreviousQuantity - CurrentQuantity as QuantityDecrease
from ProductsHistory
order by 1, 2

How can I filter only those rows with QuantityDecrease >= 20 when a WHERE or HAVING clause do not apply here?

Accepted Answer

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

select ProductID, ProductDay, QuantityDecrease
from (
    select ProductID, ProductDay, Quantity,
       sum(Quantity) over (partition by ProductID order by ProductDay
          rows between current row and current row) as CurrentQuantity,
       sum(Quantity) over (partition by ProductID order by ProductDay
          rows between 1 preceding and 1 preceding) as PreviousQuantity,
       PreviousQuantity - CurrentQuantity as QuantityDecrease
    from ProductsHistory) S
where QuantityDecrease >= 20
order by 1, 2

This returns the wanted results:

P-ID    P-Day   QuantityDecrease
400 '2010-08-25'    22.0
400 '2010-08-29'    35.0

Resume:

I'm starting to get comfortable with window expressions. (Yes, I do know there's much more on this.)

SAP SQL Anywhere

How can I filter the results of a WINDOW function?

Follow this question

Related questions