How do I use sql to select this

Question

I have the following data:

and I don't know how to use one sentence to query the continuous value(one = 1) when position is 2.the result should like this:

maxCount

   3

Answer 1

You should elaborate your logic in OP, not the comment above, this will help better, here you can try following sql and check if it's what you want or not:

SELECT
    MAX(@cnt := CASE WHEN one = 1 THEN  @cnt + 1 ELSE 0 END) AS maxCount
FROM yourtable
CROSS JOIN ( SELECT @cnt := 0 ) t
WHERE `position` = 2
ORDER BY id

Also you can check SQLFiddle Demo.