Path from a group

Question

I have the following data:

╔════╦═══════╦═══════╗
║ id ║ group ║ place ║
╠════╬═══════╬═══════╣
║  1 ║     1 ║ a     ║
║  2 ║     1 ║ b     ║
║  3 ║     1 ║ b     ║
║  4 ║     1 ║ a     ║
║  5 ║     1 ║ c     ║
║  6 ║     2 ║ a     ║
║  7 ║     2 ║ b     ║
║  8 ║     2 ║ c     ║
╚════╩═══════╩═══════╝

How can I get the path of each group in MySQL?

The expected result is:

╔═══════╦════════════╗
║ group ║    path    ║
╠═══════╬════════════╣
║     1 ║ a-b-a-c    ║
║     2 ║ a-b-c      ║
╚═══════╩════════════╝

Am I correct that, when sorted by `id`, where a `place` appears more than once in a row for the same `group`, it should only appear once in the `path`, whereas if the `place` appears again not consecutively, it should be in the `path`? For example if the sequence was `a-a-b-b-b-a-b-b-c` it should be simplified to `a-b-a-b-c`? — Matt Raines, May 03 '18 at 13:25

Matt Raines · Accepted Answer · 2018-05-04T08:52:53.240

Assuming that the end goal is to sort by group and id, and then simplify each group's sequence so that consecutive repeated places are only shown once:

Start by determining, for each row, whether the place or the group have changed since the previous row. There's a good solution to this problem in this answer.

Then use GROUP_CONCAT to merge the places together into a path.

Be aware that GROUP_CONCAT has a user-configurable maximum length, which by default is 1,024 characters.

SELECT 
    `group`,
    GROUP_CONCAT(place ORDER BY id SEPARATOR '-') path
FROM
    (SELECT 
        COALESCE(@place != place OR @group != `group`, 1) changed,
        id,
        @group:=`group` `group`,
        @place:=place place
    FROM
        place_table, (SELECT @place:=NULL, @group:=NULL) s
    ORDER BY `group`, id) t
WHERE
    changed = 1
GROUP BY `group`;

Path from a group

1 Answers1