The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
29.3k287438644
accept rate: 32%

edited 26 Sep '10, 10:53

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824


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
29.3k287438644
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:

×15
×5

question asked: 01 Sep '10, 16:41

question was seen: 1,207 times

last updated: 26 Sep '10, 10:53