MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


10.6.5-MariaDB


CREATE TABLE tbl (
  id INT PRIMARY KEY,
  amount INT NOT NULL
);
INSERT INTO tbl VALUES
(1  , 0 ),
(2  , 100    ),
(3  , 0    ),
(4  , 0  ),
(5  , 200    ),
(6  , 0    ),
(7  , 0    ),
(8  , 300  ),
(9  , 0    );
SELECT * FROM tbl


id
amount


1
0

2
100

3
0

4
0

5
200

6
0

7
0

8
300

9
0


SELECT *, sum(amount_check) over (order by id asc)  as positive_value_counter
FROM
(
SELECT *, 
CASE WHEN amount > 0 then 1 else 0 end as amount_check
FROM tbl
) s


id
amount
amount_check
positive_value_counter


1
0
0
0

2
100
1
1

3
0
0
1

4
0
0
1

5
200
1
2

6
0
0
2

7
0
0
2

8
300
1
3

9
0
0
3


SELECT
*
FROM
(
SELECT *, sum(amount_check) over (order by id asc)  as positive_value_counter
FROM
(
SELECT *, 
CASE WHEN amount > 0 then 1 else 0 end as amount_check
FROM tbl
) s
) m
WHERE
positive_value_counter >= 1 and positive_value_counter < 2


id
amount
amount_check
positive_value_counter


2
100
1
1

3
0
0
1

4
0
0
1