Detecting patterns in table

Question

Having a table like this:

Table: statuses
ID  | status
----|---------
777 | 1
675 | 1
651 | 0
611 | 1
600 | 0
554 | 1
443 | 0
323 | 0
222 | 1
112 | 1

How to select only the rows, where two (or more) statuses in the row are 0? (in the sample case only 443, 323), and group them by the first ID in series.

So the output would be:

ID  | status | group
----|--------|---------
443 | 0      | 443
323 | 0      | 443

Answer 1

If a programmatic approach is not possible, you can try this query (though it will have worse performance).

 select
    s.id,
    0 as status,
    group_field.id as group
from 
    statuses s,
    (select id from statuses where status = 0 group by id having count(1) > 1 limit 1) group_field
where 
    s.id in(select id from statuses where id = 0 group by id having count(1) > 1)

Answer 2

select 
id, STATUS,
@st_order:=if(@pre_st!=status,0,@st_order+1) as status_order,
@group_leader:=if(@pre_st!=status,id,@group_leader) as group_leader,
@pre_st:=status
from statuses,(select @st_order:=-1, @pre_st:=-1, @group_leader:=-1) val 

This sql gives you the output

id,
status,
the row number of continues same status,
the same status's group leader,
useless last column

Given your input, the first four columns output is

ID|status|status row number|status group leader
777| 1 | 0 | 777
675| 1 | 1 | 777
651| 0 | 0 | 651
611| 1 | 0 | 611
600| 0 | 0 | 600
554| 1 | 0 | 554
443| 0 | 0 | 443
323| 0 | 1 | 443
222| 1 | 0 | 222
112| 1 | 1 | 222

So you can do whatever you want in an outer select.