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      ║
╚═══════╩════════════╝

Answer 1

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`;