Sql Query To Choose Row Before Two Consecutive True Values

Here is my table. Price Volume ------------------ 60 0 70 10 80 0 90 0 100 40 200 40 300 40 400 0 500

Solution 1:

You didn't state your DBMS so this is ANSI SQL:

with flagged as (
  select price, 
         volume,
         casewhen volume + lag(volume) over (orderby price) = 0then1else0endas rn
  from prices
), counted as (
  select price, 
         volume, 
         sum(rn) over (orderby price) as cn,
         rn
  from flagged
)
select price, volume
from counted
where cn > 0and rn = 0orderby price

Here is a SQLFiddle example: http://sqlfiddle.com/#!6/b2434/4

Solution 2:

Standard SQL cannot handle this. You need a cursor to cycle through the ordered rows and decide what to output, which requires the use of some variables.

Depending on the RDBMS you are using, you could write a stored procedure that reads the rows and filters them. Otherwise you must have the application do the filtering.

Solution 3:

In sql server 2012+ you can do this with a couple LAG statements.

Lag for the 2 previous values and where when they are both 0.