How to select maximum and minimum of continuous group numbers using MySQL?

user_12682913 0
Viewed 376

I have a table with some rows, here is the table and row construction queries:

create table class_room(
    section varchar(20),
    section_name varchar(20),
    section_id varchar(20),
    student_rollno varchar(20)
);
insert into class_room values ('A','DIV A','01','547');
insert into class_room values ('A','DIV A','01','548');
insert into class_room values ('A','DIV A','01','549');
insert into class_room values ('A','DIV A','01','550');
insert into class_room values ('A','DIV A','01','551');
insert into class_room values ('A','DIV A','01','552');
insert into class_room values ('A','DIV A','01','567');
insert into class_room values ('A','DIV A','01','568');
insert into class_room values ('A','DIV A','01','593');
insert into class_room values ('A','DIV A','01','594');
insert into class_room values ('A','DIV A','01','595');
insert into class_room values ('A','DIV A','01','596');
insert into class_room values ('A','DIV A','01','597');
insert into class_room values ('A','DIV A','01','598');

Here user wants to see the ranges of student_rollno for certain section. So I have tried the below code:

select section,section_name,section_id,
        concat_ws('-',min(student_rollno),max(student_rollno)) as rollno_range 
from class_room 
where section='A' 
group by section_id 
having count(*)>=1;

The above code give me this result:

+---------+--------------+------------+--------------+
| section | section_name | section_id | rollno_range |
+---------+--------------+------------+--------------+
| A       | DIV A        | 01         | 547-598      |
+---------+--------------+------------+--------------+

But user wants to see the discrete values, which will looks like below solution:

+---------+--------------+------------+--------------------------------+
| section | section_name | section_id | rollno_range                   |
+---------+--------------+------------+--------------------------------+
| A       | DIV A        | 01         | 547-552, 567-568, 593-598      |
+---------+--------------+------------+--------------------------------+

I couldn't understand how to get output in above mentioned fashion. Please help.

mysql sql
1 Answers

This is a type of gaps-and-islands problem. You can get adjacent values with no gaps by subtracting a sequence number from the roll no:

select section, section_name, section_id, min(student_rollno), max(student_rollno)
from (select cr.*,
             row_number() over (partition by section, section_name, section_id order by student_rollno) as seqnum
      from class_room cr
     ) cr
group by section, section_name, section_id, (student_rollno - seqnum);

Then you can aggregate this again:

select section, section_name, section_id,
       group_concat(min_sr, '-', max_sr order by min_sr separator ', ')
from (select section, section_name, section_id,
             min(student_rollno) as min_sr, max(student_rollno) as max_sr
      from (select cr.*,
                   row_number() over (partition by section, section_name, section_id order by student_rollno) as seqnum
            from class_room cr
           ) cr
      group by section, section_name, section_id, (student_rollno - seqnum)
     ) ss
group by section, section_name, section_id;

Here is a db<>fiddle.

In older versions of MySQL, you can emulate this using variables. This should work under most circumstances:

select section, section_name, section_id,
       group_concat(min_sr, '-', max_sr order by min_sr separator ', ')
from (select section, section_name, section_id,
             min(student_rollno) as min_sr, max(student_rollno) as max_sr
      from (select cr.*, (@rn := @rn + 1) as seqnum
            from (select cr.*
                  from class_room cr
                  order by section, section_name, section_id, student_rollno
                 ) cr cross join
                 (select @rn := 0) params
           ) cr
      group by section, section_name, section_id, (student_rollno - seqnum)
     ) ss
group by section, section_name, section_id;
user_11440350
Related