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

Can be done programmatically based on what language you wanna use? — Subhan, Apr 16 '15 at 08:55
SQL is not the appropriate tool for this purpose. Select all the rows that qualify then filter and group them in the application code. — axiac, Apr 16 '15 at 09:07
how is your data sorted? if there is not another field indicating the order (so you are relying on insertion order) you won't get consistent results. tables are sets, the rows have no intrinsic order unless specified. — 1010, Apr 17 '15 at 01:41

score 2 · Answer 1 · edited Apr 17 '15 at 00:47

2

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)

edited Apr 17 '15 at 00:47

The name's Bob. MS Bob.

859
9
22

answered Apr 17 '15 at 00:22

Carlos Meseguer

46
4

score 1 · Answer 2 · answered Apr 16 '15 at 09:27

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.

see [this](http://stackoverflow.com/questions/16715504/mysql-define-a-variable-within-select-and-use-it-within-the-same-select/16715618#16715618). the order in which multiple uses of a variable in a query are evaluated is not guaranteed. — 1010, Apr 17 '15 at 13:04

mahesh · Answer 3 · 2015-04-16T10:38:32.070

You can achieve this by using little logic using sub queries

set @cnt=0;
set @id=-1;
SELECT 
    t3.id, t3.status, t3.dup_id AS `group`
FROM
    (SELECT 
        id,
            status,
            IF(status = 0, @cnt:=@cnt + 1, @cnt:=0) AS great,
            IF(@cnt = 1, @id:=id, IF(status = 0, @id, - 1)) AS dup_id
    FROM
        statuses) t3
WHERE
    t3.dup_id IN (SELECT 
            t1.dup_id
        FROM
            (SELECT 
                id,
                    status,
                    IF(status = 0, @cnt:=@cnt + 1, @cnt:=0) AS great,
                    IF(@cnt = 1, @id:=id, IF(status = 0, @id, - 1)) AS dup_id
            FROM
                statuses) t1
        GROUP BY t1.dup_id
        HAVING COUNT(t1.dup_id) > 1
            AND t1.dup_id != - 1);

Result will be as you expected

id  | status | group
----|--------|---------
443 | 0      | 443
323 | 0      | 443

too lengthy, complicated, try once again but in a simple way. — A_Sk, Apr 16 '15 at 10:45

Detecting patterns in table

3 Answers3