FREE AI-Enhanced Online SQLite Compiler

Untitled*

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

EXPLAIN QUERY PLAN
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