CODEWITHSUNDEEP
mysqlsql
Protected
Protected

Reputation: 362

Selecting groups of consecutive records with a common attribute?

I'm looking for a query capable of selecting from a single table in such a way that consecutive records for which an attribute is equal are collapsed together. Similar to group by, but instead of grouping every occurence of the attribute together, I want one group for each consecutive range.

Example table:

+-----+-----+
|order|group|
+-----+-----+
|1    |aaa  |
+-----+-----+
|2    |aaa  |
+-----+-----+
|3    |bbb  |
+-----+-----+
|4    |aaa  |
+-----+-----+
|5    |aaa  |
+-----+-----+
|6    |aaa  |
+-----+-----+
|7    |ccc  |
+-----+-----+
|8    |aaa  |
+-----+-----+

Example desired result:

+-----+-------------------+
|group|group_concat(order)|
+-----+-------------------+
|aaa  |1,2                |
+-----+-------------------+
|bbb  |3                  |
+-----+-------------------+
|aaa  |4,5,6              |
+-----+-------------------+
|ccc  |7                  |
+-----+-------------------+
|aaa  |8                  |
+-----+-------------------+

I can't use stored procedures.

I have a vague notion I will need at least one level of nesting for sorting the table (probably more in total), and probably have to use variables, but no more than that. Please let me know if you need further details.

EDIT: Queries for creating example:

create temporary table tab (
    ord int,
    grp varchar(8)
);

insert into tab (ord, grp) values
(1, 'aaa'),
(2, 'aaa'),
(3, 'bbb'),
(4, 'aaa'),
(5, 'aaa'),
(6, 'aaa'),
(7, 'ccc'),
(8, 'aaa');

Upvotes: 3

Views: 1190

Answers (3)

cardeol
cardeol

Reputation: 2238

Use:

    select pquery.group, GROUP_CONCAT(pquery.order separator ', ') as order FROM
(  select t.*, @lastSeq := if( t.group != @lastGroup                                 
                      , 0, @lastSeq + 1 )
               as groupSeq,
     @lastGroup := t.group,
     @cont := if(@lastSeq =0, @cont + 1, @cont) as counter,
     @cgroup := concat(@cont,t.group) as cgroup
   from t, ( select @lastGroup := '',
   @lastSeq := 0, @cont :=0) sqlVars
 ) pquery 
group by pquery.cgroup

Be carfeul with the variable group_concat_max_len=1024 (result limit in bytes). Change depending on your needs.

Upvotes: 0

Jason Heo
Jason Heo

Reputation: 10236

Could you try this? You can test here http://www.sqlfiddle.com/#!2/57967/12.

Select grp_new, group_concat(ord)
From (
   Select ord, if(grp = @prev, @seq, @seq := @seq + 1) as seq,
    if(grp = @prev, grp, @prev := grp) as grp_new
  From tab, (SELECT @seq := 0, @prev := '') AS init
  Order by ord
) x
Group by grp_new, seq;

The key idea is generate same seq for same consecutive group as follows.

Select
   ord, if(grp = @prev, @seq, @seq := @seq + 1) as seq,
    if(grp = @prev, grp, @prev := grp) as grp_new
From tab, (SELECT @seq := 0, @prev := '') AS init
Order by ord

then finally grouping GROUP BY grp, seq which can differenciate each consecutive groups even if they have same grp.

EDIT: To get exactly the result in the example:

Select grp_new, group_concat(ord order by ord)
From (
  Select ord, if(grp = @prev, @seq, @seq := @seq + 1) as seq,
    if(grp = @prev, grp, @prev := grp) as grp_new
  From tab, (SELECT @seq := 0, @prev := '') AS init
  Order by ord
) x
Group by seq

Upvotes: 1

Jonnymaboy
Jonnymaboy

Reputation: 543

I would create a temp table with all columns above and add 2 columns id int (autoincrement for looping) groupid int and insert your above results into it, then update the new groupid column with an int value, this will create the grouping you need see below..

Once you have the temp table populated Using a while loop, loop the records in the temp table. The temp table needs an incremented id column with no gaps ie 1,2,3,4,5,6,7,8.... whatever your results go to.

inside the loop check if this row's group = last rows group if yes set groupid to groupid else set to groupid+1. and reset groupid to be groupid=groupid+1.

so for your eaxample groupid starts with 0. then reads first row and sets groupid to groupid(0). then read row 2 check if group = last rows groupd > yes it does so row>group id = groupid.

read row 3 does group match last row group > no > groupid = groupid+1 then set groupid to the groupid.

check 4,5,6,7 etc.

Once all done then just group by the new column and you will have the results you desire. Hope this helps. I cant see anyother way of doing this without the loop

Upvotes: 0

Related Questions