Mysql group_concat limit rows in grouping

Question

The next example is my database.

tb_port
id  port
1   80
2   22
3   53
4   3128
5   443

tb_dest
id  dest
1   network
2   local

tb_rule
id  id_port id_dest
1   1       1
2   2       1
3   3       1
4   4       1
5   5       1

Select: select dest,group_concat(port) from tb_port a, tb_dest b, tb_rule c where a.id=c.id_port and b.id=c.id_dest group by dest

Result: network 80,22,53,3128,443

but is not the result I'm looking for, the result would be this.

Select ex: select dest,group_concat(port limit 2) from tb_port a, tb_dest b, tb_rule c where a.id=c.id_port and b.id=c.id_dest group by dest

result I would like

network    80,22
network 53,3128
network 443

how to achieve this result only with SQL?

Sqlfiddle: http://sqlfiddle.com/#!2/d11807

How many ports would you get?More than 5?What would be the maximum? — Mihai
– Mihai, Commented Feb 4, 2015 at 19:19

Joachim Isaksson · Accepted Answer · 2015-02-04 20:18:48Z

2

MySQL doesn't make this kind of query easy, but one (admittedly not very pretty) solution is to use a variable to give each row a sequence number per dest and just group by the row number integer divided by 2 to get two numbers in each group;

SELECT dest, GROUP_CONCAT(port ORDER BY rank) ports
FROM (
  SELECT dest, port, ( 
      CASE dest WHEN @curDest 
                THEN @curRow := @curRow + 1 
                ELSE @curRow := 1 AND @curDest := dest END) rank
  FROM tb_port a
  JOIN tb_rule c ON a.id = c.id_port
  JOIN tb_dest b ON b.id = c.id_dest, 
    (SELECT @curRow := 0, @curDest := '') r
  ORDER BY dest
) z
GROUP BY FLOOR(rank/2),dest
ORDER BY dest, MIN(rank)

An SQLfiddle to test with.

answered Feb 4, 2015 at 20:18

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marcos Carraro Over a year ago

thanks Joachim Isaksson : D this select é very simple and objective.

Mihai · Accepted Answer · 2015-02-04 20:33:01Z

1

Here is a stored proc,you just put in the delimiter when you call it

DELIMITER $$

DROP PROCEDURE IF EXISTS explode_table $$
CREATE PROCEDURE explode_table(bound VARCHAR(255))

  BEGIN

    DECLARE id TEXT;
    DECLARE value TEXT;
    DECLARE occurance INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE splitted_value TEXT;
    DECLARE done INT DEFAULT 0;
    DECLARE cur1 CURSOR FOR  
     select dest,group_concat(port) from tb_port a, tb_dest b, tb_rule c 
     where a.id=c.id_port and b.id=c.id_dest and  dest != '' group by dest;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    DROP TEMPORARY TABLE IF EXISTS table2;
    CREATE TEMPORARY TABLE table2(
    `id` VARCHAR(255),
    `value` VARCHAR(255) NOT NULL
    ) ENGINE=Memory;

    OPEN cur1;
      read_loop: LOOP
        FETCH cur1 INTO id, value;
        IF done THEN
          LEAVE read_loop;
        END IF;

        SET occurance = (SELECT LENGTH(CONCAT(value,bound))
                                 - LENGTH(REPLACE(CONCAT(value,bound), bound, ''))
                                 +1);
        SET i=2;
        WHILE i <= occurance DO
          SET splitted_value =
          SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(value,bound),bound,i),bound,-2) ;

          INSERT INTO table2 VALUES (id, splitted_value);
          SET i = i + 2;

        END WHILE;
      END LOOP;

      SELECT * FROM table2;
    CLOSE cur1;
  END; $$



 CALL explode_table(',')

answered Feb 4, 2015 at 20:33

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

1 Comment

Marcos Carraro Over a year ago

thanks for the reply , but I preferred the previous post , it is simpler, although his is more "professional" , thanks again .

Collectives™ on Stack Overflow

Mysql group_concat limit rows in grouping

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related