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.