Naren Naren - 4 months ago 20
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

userid
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

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, 
       p.*
FROM p,
(SELECT @rownum := 0) r
  ) g
where userid = 100

Here is a functional example