Naren Naren - 1 year ago 58
SQL Question

Mysql select query until reach the condition

Lets say I have list of users in my user table like below. I need to find the count of users from my table until the

is equal to 100.

So here the answer is (3). But how can i find this is MySQL query. Any idea?

userid name
10 aaa
30 bbb
100 ccc
60 ddd

Answer Source

This is difficult to do in MySql as the 'first' instance of a number can be difficult to pin down.

One way to resolve this would be to create row numbers, then choose the minimum row number with Userid = 100

select min(row_number) from
SELECT @rownum:=@rownum + 1 as row_number, 
(SELECT @rownum := 0) r
  ) g
where userid = 100

Here is a functional example