Count rows until value in column changes mysql

Question

So I have a table with a column that has a true or false (0 or 1) value. I want to know the count of consecutive 0s before I hit a 1. So for example if I had the following time ASC:

0
0
1
0
1
0
0
0

The return number of last consecutive 0s would be 3. I've been looking for a way to count until the value changes but so far no luck. Any thoughts?

EDIT:

here's a clearer example of my table

|id|counter|user_id|
--------------------
|0 |0      |3      | 
--------------------
|1 |0      |3      | 
--------------------
|2 |1      |3      | 
--------------------
|3 |0      |3      | 
--------------------
|4 |1      |3      | 
--------------------
|5 |0      |3      | 
--------------------
|6 |1      |8      | 
--------------------
|7 |0      |3      | 
--------------------
|8 |0      |3      | 
--------------------

for user_id = 3, the result would be 3

Answer 1

Your time column seems to specify the ordering. Then the number of zeros at the end would be:

select count(*)
from table t
where t.col = 0 and
      t.time > (select max(t2.time) from table t2 where t2.col = 1);