Select rows until a condition is met Given:

CREATE TABLE T
(  x int not null primary key 
,  y int not null );

INSERT T(x,y)
VALUES (1, 0)
  ,(2, 0)
  ,(3, 0)
  ,(4, 0)
  ,(5, 3)
  ,(6, 0)
  ,(7, 4)
  ,(8, 0)
  ,(9, 0);


The requirement is to return rows (ordered by x) where y = 0, until a row is encountered where y is not equal to zero.

The expected result for the data above is to return the rows where x is between 1 and 4. The other rows where y = 0 should not be returned, because they come after the row where x = 5 and y = 3.
First a note: when relational databases filter rows, they check each row separately if it fulfils given conditions. Other rows from the same table are not taken into account at that moment but again checked separately for the same (row-independent) conditions. So there is no "simple" command for what you want.

But there are multiple ways to accomplish it:


Use a variable which is initialized to true and then for each row it is used in the filter and set to @var = @var AND (y = 0) - that way first encountered non-zero value will set the variable to false and no other row can fulfil the condition. There is a problem with this - setting a value of user variable in the same statement as reading it is sort of undefined behavior - it often works but it may not work sometimes..
select the lowest x for which the y is non-zero select min(x) from T where y &gt; 0; or select x from T where y &gt; 0 order by x asc limit 1; - keep the value in app or in variable and then just select all the rows where x is less than the value you found select x, y from T where x &lt; @stored_x order by x asc;
instead of app/sql variable use a subquery for the previous - select x, y from T where x &lt; (select min(x) from T where y &gt; 0) order by x asc; - this uses a sependent subquery which may be evaluated once per each row if the server version does not implement subquery caching or some kind of materialization so it might be slow
as previous but use join and a derived table (which is evaluated only once and materialized into a temp table):


-

select T.x, T.y
from T
join (
    select min(x) as limit
    from T
    where y &gt; 0
) T2
where T.x &lt; T2.limit
order by T.x asc;


The important thing is - you cannot think of it as "Select rows until some condition is met." but as "Which rows do meet this condition? The ones where X is smaller than that of a(ny) row with nonzero Y." and write that using standard SQL.