SQL query to choose row before two consecutive true values

Question

Here is my table.

Price   Volume
------------------
60          0
70          10
80          0
90          0
100         40
200         40
300         40
400         0
500         50
600         60

It is ordered by Price.

I need to choose all rows before two consecutive zeros in Volume column. So the result should look like

Price   Volume
------------------
100         40
200         40
300         40
400         0
500         50
600         60

I really do not know where to start with this type of query. I could only think go pulling data into C# and the loading back into table.

Regards,

which DBMS are you using? DBs are not well suited to do this type of thing, but there are options. — attila, Apr 12 '14 at 21:44
Doing this thing in C# once you get the data isn't an option? — NiVeR, Apr 12 '14 at 21:45
Your example result is actually all rows **after** two consecutive zeros, not *before*. — , Apr 12 '14 at 21:45

score 2 · Answer 1 · 2014-04-12T22:10:57.450

2

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

with flagged as (
  select price, 
         volume,
         case 
           when volume + lag(volume) over (order by price) = 0 then 1
           else 0
         end as rn
  from prices
), counted as (
  select price, 
         volume, 
         sum(rn) over (order by price) as cn,
         rn
  from flagged
)
select price, volume
from counted
where cn > 0 and rn = 0
order by price

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

edited Apr 12 '14 at 22:10

answered Apr 12 '14 at 21:57

Excellent solution! Also great example of LAG usage. At the very end I don't think you need "volume > 0". You will miss value price = 400 and volume = 0. Do you know how time efficient usage of LAG or LEAD? – user1700890 Apr 12 '14 at 23:07
@user1700890: I changed the `volume = 0` – Apr 13 '14 at 07:23

score 1 · Answer 2 · answered Apr 12 '14 at 21:45

1

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.

answered Apr 12 '14 at 21:45

Frazz

2,995
2
19
33

I guess I will end up doing it in C#. I just want to make sure that LEAD and LAG would not work here.[link](http://stackoverflow.com/questions/3298526/how-can-i-find-duplicate-consecutive-values-in-this-table) – user1700890 Apr 12 '14 at 21:49
You want to make sure that it doesn't work? Why is that? – fancyPants Apr 12 '14 at 21:54
Standard SQL *can* handle this. – Apr 12 '14 at 21:58
@fancyPants. Before I start writing C# code, I want to make sure that I tried everything in SQL. I poorly worded it: if LEAD and LAG work, I do not need to write C# code. – user1700890 Apr 12 '14 at 22:03
Since when di LAG and LEAD make it into standard ANSI SQL? Anyway they are available in MS SQL Server, so go for it. – Frazz Apr 12 '14 at 22:11
`lead()` and `lag()` made it into the standard about 11 years ago when SQL:2003 was introduced. And e.g. Oracle was able to do that for nearly 20 years – Apr 12 '14 at 22:11

score 0 · Answer 3 · answered Apr 12 '14 at 21:47

0

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.

answered Apr 12 '14 at 21:47

Karl Kieninger

8,841
2
33
49

SQL query to choose row before two consecutive true values

3 Answers3

Linked