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