Recursive join and group_concat with SQLite-CodePudding

I have the following table:

id  value  successor_id
--  -----  ------------
 1     v1             2
 2     v2             4
 4     v3          null
 7     v4             9
 9     v5          null
12     v6          null

Note: Those are simple paths (no trees), so two ids can not have the same successor_id. Also, the ids are ordered and a successor must be the following id. E.g. in my example the only possible successor of id 2 is 4. It cannot be 1 or 7.

Now, I want to do some kind of recursive LEFT JOIN ON id = successor_id with the table itself and GROUP_CONCAT the values in order to get the following result:

min_id  max_id    values
------  ------  --------
     1       4  v1,v2,v3
     7       9  v4,v5
    12      12  v6

How can I achieve this? I guess a combination of WITH RECURSIVE and GROUP BY, but I don't know how to start since WITH RECURSIVE requires a starting point, but I have multiple starting points (ids: 1, 7 and 12).

Here is the SQL code to create the example table:

.mode column
.nullvalue null
.width -1 -1 -1

CREATE TABLE test (
    id INTEGER NOT NULL,
    value STRING NOT NULL,
    successor_id INTEGER
);

INSERT INTO test (id, value, successor_id) VALUES ( 1, 'v1',    2);
INSERT INTO test (id, value, successor_id) VALUES ( 2, 'v2',    4);
INSERT INTO test (id, value, successor_id) VALUES ( 4, 'v3', null);
INSERT INTO test (id, value, successor_id) VALUES ( 7, 'v4',    9);
INSERT INTO test (id, value, successor_id) VALUES ( 9, 'v5', null);
INSERT INTO test (id, value, successor_id) VALUES (12, 'v6', null);

SELECT id, value, successor_id
FROM test;

CodePudding user response：

There is no need for a recursive query.
Use window functions:

WITH cte AS (
  SELECT *, LAG(successor_id) OVER (ORDER BY id) IS NULL flag
  FROM test
)
SELECT DISTINCT
       MIN(id) OVER (PARTITION BY grp) min_id,
       MAX(id) OVER (PARTITION BY grp) max_id,
       GROUP_CONCAT(value) OVER (
         PARTITION BY grp 
         ORDER BY id 
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) "values" 
FROM (
  SELECT *, SUM(flag) OVER (ORDER BY id) grp
  FROM cte
);

See the demo.