sql - Group sequence value in column mysql

Question

Table mytbl have two columns: col_1 & col_2. I wanted to group range of values in col_1 for single col_2 value.

Example:

col_1	col_2
1	3
2	1
3	3
4	3
5	2
7	3
8	3
9	3
10	1

Answer 1

This is a gaps and island problem. Here is an approach using the difference between row numbers to identify the groups:

select 
    min(col_1) as start_col_1, 
    case when max(col_1) <> min(col_1) then max(col_1) end as end_col_1, 
    col2
from (
    select t.*,
        row_number() over(partition by col2 order by col_1) as rn
    from mytable t
) t
where col_2 = 3
group by col2, col_1 - rn
order by start_col1

This returns null rather than '-' when the islands is made of just one record (that is because the latter is not a valid number).

This works as long as col_1 increments without gaps. Else, we can generate our own sequence with another row_number():

select 
    min(col_1) as start_col_1, 
    case when max(col_1) <> min(col_1) then max(col_1) end as end_col_1, 
    col2
from (
    select t.*,
        row_number() over(order by col_1) as rn1,
        row_number() over(partition by col2 order by col_1) as rn2
    from mytable t
) t
where col_2 = 3
group by col2, rn1 - rn2
order by start_col1