Selecting all rows until first occurrence of given value

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

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.

Tags:

Sql

Oracle