FREE AI-Enhanced Online SQLite Compiler

Untitled*

You must execute the code again to save those values in the database.

WITH
elements
    (grp,value) AS ( VALUES
    ('a',1    ),
    ('a',2    ),
    ('b',9    ),
    ('b',3    ),
    ('b',2    )
),
base AS
(
    SELECT
        grp  ,
        value,
        COUNT(*) OVER (
            PARTITION BY grp) AS grp_count
    FROM
        elements
),
combinations AS
(
    SELECT
                       grp            ,
                       value          ,
        grp_count   AS partition_index,
        value || '' AS path
    FROM
        base

UNION

SELECT
                          prior.grp            ,
                        current.value          ,
        partition_index - 1  AS partition_index,
        path          ||
        '.'           ||
        current.value        AS path
    FROM
        combinations AS prior
    JOIN
        base AS current
        ON
        prior.grp IS current.grp
    WHERE
        partition_index >  1             AND
        prior.value     >= current.value
),
combination_sets AS
(
    SELECT
        grp,
        path
    FROM
        combinations
    WHERE
        partition_index IS 1
)
SELECT
    combination_sets.grp,
    combination_sets.path,
    value
FROM
    combination_sets
JOIN
    combinations
    ON
    combination_sets.grp  IS   combinations.grp           AND
    combination_sets.path LIKE (combinations.path || '%')
ORDER BY
    combination_sets.grp,
    combination_sets.path

grp	path	value
a	1.1	1
a	1.1	1
a	2.1	1
a	2.1	2
a	2.2	2
a	2.2	2
b	2.2.2	2
b	2.2.2	2
b	2.2.2	2
b	3.2.2	2
b	3.2.2	2
b	3.2.2	3
b	3.3.2	2
b	3.3.2	3
b	3.3.2	3
b	3.3.3	3
b	3.3.3	3
b	3.3.3	3
b	9.2.2	2
b	9.2.2	2
b	9.2.2	9
b	9.3.2	2
b	9.3.2	3
b	9.3.2	9
b	9.3.3	3
b	9.3.3	3
b	9.3.3	9
b	9.9.2	2
b	9.9.2	9
b	9.9.2	9
b	9.9.3	3
b	9.9.3	9
b	9.9.3	9
b	9.9.9	9
b	9.9.9	9
b	9.9.9	9