CODEWITHSUNDEEP
mysqlsqljoin
dimaninc
dimaninc

Reputation: 850

mysql: group surrounding records by same field value

I have a table with a following structure and data:

id | type | title
--------------------------
1  | 1    | test 1
2  | 1    | test 2
3  | 2    | test 3
4  | 2    | test 4
5  | 1    | test 5

I need to group neighbor rows with the same type field values.

So the result should be like:

type |
------
1    |
2    |
1    |

Thanks in advance.

Upvotes: 0

Views: 341

Answers (2)

Strawberry
Strawberry

Reputation: 33945

Here's one way - although a solution using variables will scale better...

 DROP TABLE IF EXISTS my_table;

 CREATE TABLE my_table
 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY 
 ,type INT NOT NULL 
 ,title VARCHAR(12) NOT NULL
 );

 INSERT INTO my_table VALUES
 (1,1,'test 1'),
 (3,1,'test 2'),
 (4,2,'test 3'),
 (7,2,'test 4'),
 (9,1,'test 5');

 SELECT * FROM my_table;
 +----+------+--------+
 | id | type | title  |
 +----+------+--------+
 |  1 |    1 | test 1 |
 |  3 |    1 | test 2 |
 |  4 |    2 | test 3 |
 |  7 |    2 | test 4 |
 |  9 |    1 | test 5 |
 +----+------+--------+  

 SELECT a.id start
      , MIN(c.id) End 
      , a.type
   FROM 
      ( SELECT x.*,COUNT(*) rank FROM my_table x JOIN my_table y ON y.id <= x.id GROUP BY x.id) a
   LEFT 
   JOIN 
      ( SELECT x.*,COUNT(*) rank FROM my_table x JOIN my_table y ON y.id <= x.id GROUP BY x.id) b 
     ON b.type = a.type
    AND b.rank = a.rank - 1
   LEFT 
   JOIN 
      ( SELECT x.*,COUNT(*) rank FROM my_table x JOIN my_table y ON y.id <= x.id GROUP BY x.id) c 
     ON c.type = a.type
    AND c.rank >= a.rank 
   LEFT 
   JOIN 
      ( SELECT x.*,COUNT(*) rank FROM my_table x JOIN my_table y ON y.id <= x.id GROUP BY x.id) d 
     ON d.type = a.type
    AND d.rank = c.rank + 1
  WHERE b.id IS NULL 
    AND c.id IS NOT NULL
    AND d.id IS NULL
  GROUP 
     BY a.id; 
+-------+------+------+
| start | End  | type |
+-------+------+------+
|     1 |    3 |    1 |
|     4 |    7 |    2 |
|     9 |    9 |    1 |
+-------+------+------+

Upvotes: 0

John Ruddell
John Ruddell

Reputation: 25862

this should do the trick.. using user defined variables.

SELECT 
    type 
FROM(
    SELECT 
        type,
        if(@a = type, @b, @b := @b + 1) as grouping_col,
        @a := type
    FROM testing
    JOIN (select @a := 1, @b := 0) as temp
) as t
GROUP BY grouping_col;

SQL FIDDLE to play with

Upvotes: 1

Related Questions