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:

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?

asked 01 Sep '10, 16:41

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

edited 26 Sep '10, 10:53

Breck%20Carter's gravatar image

Breck Carter
26.2k430600863


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.)

permanent link

answered 01 Sep '10, 16:49

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×16
×6

question asked: 01 Sep '10, 16:41

question was seen: 1,281 times

last updated: 26 Sep '10, 10:53