CODEWITHSUNDEEP
mysqlsql
Cropeck
Cropeck

Reputation: 11

MySQL: select consecutive ID's as range

Having this table

+-----+------+
|   ID|Status|
+-----+------+
|    1|     0|
|    2|     1|
|    3|     1|
|    4|     0|
|    5|     1|
|    6|     1|
|    7|     1|
|    8|     1|
|    9|     0|
|   10|     1|
|   12|     1|
+-----+------+

I want to select the ID's where Status = 1 as ranges of consecutive values:

+-----+-----+
|  Low| High|
+-----+-----+
|    2|    3|
|    5|    8|
|   10|   10|
|   12|   12|
+-----+-----+

I'm not sure how to approach this.

Upvotes: 0

Views: 1212

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You can do this by assigning a group to each row. One method uses a correlated subquery to count the number of different statuses before any given one:

select status, min(id) as low, max(id) as high
from (select t.*,
             (select count(*)
              from t t2
              where t2.id < t.id and t2.status <> t.status
             ) as grp
      from t
     ) t
group by status, grp;

You can add where to either the inner or outer query to limit to status = 1.

EDIT:

If performance is an issue, then variables are going to be faster. I think the simplest method is the version suggested here -- count the number of non-1 values:

select status, min(id) as low, max(id) as high
from (select t.*,
             (@grp := (status <> 1)) as grp
      from t cross join 
           (select @grp := 0) params
      order by id
     ) t
where status = 1
group by status, grp;

This can also make use of an index on t(id, status).

Upvotes: 2

Wuodan
Wuodan

Reputation: 16

Sorry, misread MySQL for MSSQL. Seems MySQL does not support recursive CTE.

Using a recursive CTE:

declare @data table(
    ID int not null primary key,
    Status bit not null)

insert into @data
values
(1, 0),
(2, 1),
(3, 1),
(4, 0),
(5, 1),
(6, 1),
(7, 1),
(8, 1),
(9, 0),
(10, 1),
(12, 1)

;
with dataRecursive as(
    -- anchor
    select      data.ID low,
                data.ID
    from        @data data
    where       data.Status = 1
                and
                not exists(
                    select      1
                    from        @data data_previous
                    where       data_previous.Status = 1
                                and
                                data_previous.ID = data.ID -1
                )
    union all
    -- recursion
    select      dataRecursive.low,
                data_next.ID
    from        @data data_next
                inner join
                dataRecursive
                on  dataRecursive.ID = data_next.ID - 1
    where       data_next.Status = 1
)

select      low,
            MAX(ID) as high
from        dataRecursive
group by    low
order by    low

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10701

Try to use a variable and GROUP BY

SELECT MIN(id) AS low ,MAX(id) AS high
FROM
(
    SELECT 
        @row_number:=CASE
            WHEN @id = status THEN @row_number
            ELSE @row_number + 1
        END AS num,
        @id:=status as status,
        id
    FROM tab, (SELECT @row_number:=0, @id := 1) t
    ORDER BY id
) t
WHERE t.status = 1
GROUP BY num

demo

Upvotes: 0

Related Questions