CODEWITHSUNDEEP
sqloracle-database
Kamil Zadora
Kamil Zadora

Reputation: 2397

Selecting all rows until first occurrence of given value

For following data:

date|value|check
2009 | 5 | 1
2008 | 5 | 1
2007 | 5 | 1
2006 | 5 | 0
2005 | 5 | 0
2004 | 5 | 1
2003 | 5 | 1
2002 | 5 | 1

I need to select all rows from 2009 back until first occurrence of 0 in check column:

date|value|check
2009 | 5 | 1
2008 | 5 | 1
2007 | 5 | 1

I tried with the lag function, but I was only able to check a month back.

I am working on Oracle 10g.

UPDATE:

All seems to work well, my test data set is too small to say anything about the performance differences.

Upvotes: 14

Views: 7433

Answers (3)

Quassnoi
Quassnoi

Reputation: 425251

SELECT  *
FROM    (
        SELECT  m.*,
                MIN(CASE WHEN check = 0 THEN 0 ELSE 1 END) OVER (ORDER BY date DESC)) AS mn
        FROM    mytable
        )
WHERE   mn = 1

or even better:

SELECT  *
FROM    (
        SELECT  m.*, ROW_NUMBER() OVER (ORDER BY mydate DESC) AS rn
        FROM    mytable m
        ORDER BY
                mydate DESC
        )
WHERE   rownum = DECODE(check, 0, NULL, rn)
ORDER BY
        mydate DESC

The latter query will actually stop scanning as soon as it encounters the first zero in check.

Upvotes: 4

kenj0418
kenj0418

Reputation: 6838

SELECT * FROM mytable where date > (
   SELECT max(date) FROM mytable where check = 0    
)

Upvotes: 20

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

DECLARE @mytable TABLE (date integer, [value] integer, [check] integer)  

INSERT INTO @mytable VALUES (2009, 5, 1)  
INSERT INTO @mytable VALUES (2008, 5, 1)  
INSERT INTO @mytable VALUES (2007, 5, 1)  
INSERT INTO @mytable VALUES (2006, 5, 0)  
INSERT INTO @mytable VALUES (2005, 5, 0)  
INSERT INTO @mytable VALUES (2004, 5, 1)  
INSERT INTO @mytable VALUES (2003, 5, 1)  
INSERT INTO @mytable VALUES (2002, 5, 1)  

SELECT *  
FROM @mytable  
WHERE date > (SELECT MAX(date) FROM @mytable WHERE [Check] = 0)

Upvotes: 1

Related Questions