view raw
Naren Naren - 10 months ago 48
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


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