Forum Stats

  • 3,817,470 Users
  • 2,259,339 Discussions
  • 7,893,789 Comments

Discussions

LISTAGG with DISTINCT option

ApexBine
ApexBine Member Posts: 153 Silver Badge
edited Jul 31, 2019 5:46PM in Database Ideas - Ideas

I love the LISTAGG function which makes my life a lot easier.

And I would love it even more if it was able to omit duplicates!

So instead of writing

select name, signature, type, listagg(usage,', ') within group (order by usage) usages

from sys.all_identifiers

where owner     = 'ME'

and object_name = 'PKG_EXAMPLE'

and object_type = 'PACKAGE BODY'

group by name, signature, type

which would return something like

'DECLARATION, REFERENCE, REFERENCE, REFERENCE, REFERENCE, REFERENCE'

I would write

select name, signature, type, listagg(usage,', ' DISTINCT) within group (order by usage) usages

from sys.all_identifiers

where owner     = 'ME'

and object_name = 'PKG_EXAMPLE'

and object_type = 'PACKAGE BODY'

group by name, signature, type

and expect

'DECLARATION, REFERENCE'.

What do you think of it? Would it be a useful amendment?

This feature is coming with 19c:

SQL> select deptno, listagg (sal,', ') within group (order by sal)  2  from scott.emp  3  group by deptno;    DEPTNO LISTAGG(SAL,',')WITHINGROUP(ORDERBYSAL)---------- ---------------------------------------        10 1300, 2450, 5000        20 800, 1100, 2975, 3000, 3000        30 950, 1250, 1250, 1500, 1600, 2850SQL> select deptno, listagg (distinct sal,', ') within group (order by sal)  2  from scott.emp  3  group by deptno;    DEPTNO LISTAGG(DISTINCTSAL,',')WITHINGROUP(ORDERBYSAL)---------- -----------------------------------------------        10 1300, 2450, 5000        20 800, 1100, 2975, 3000        30 950, 1250, 1500, 1600, 2850
JagadekaraSven W.ApexBineAkshsBPeaslandDBAberxMartin Preissfac586MKJ10930279BeGinTPD-OpitzctriebborneselNSK2KSNMatthiasRogelFatMartinRPeter 3051993HarbourGhostMeeuwtjeRichard SmithPeter.NDavid Krch-Oracletonibony7user13364405gkbKayKScott WesleyRainer StenzelAbhinav B.GregVsensoftAparna Dutta-Oracleuser2916724Lukas EderToddBarrygdanbyCarlosDLGVysakh Suresh - 3035408tns42angelo.stramierijaramillNikolaus ThieljpschneiderLaurent SchneiderUwe Küchler [OC]1244776Marwimkcelikanasaziiulohmanncommi235Erik van RoonManikOren Nakdimonpattonjg3397048Peter Hraškolead1111Stew AshtondbitmanThorsten KettnerDinidu HewageSpike Houseuser10460036LauryJ. Fuda-Oraclesbird-OraclegassenmjDaniel E.unpstepMustafa_KALAYCIAlbert Nelson AcormacoUser_W58DOL. FernigriniSolomon Yakobsonuser1609428C. MaßnickAndrewSayerGerald Venzl-Oracle
78
82 votes

Delivered · Last Updated

«1345

Comments

  • MKJ10930279
    MKJ10930279 Member Posts: 244

    NOT WORKING IN 11.2.0.3.

    even the columns mentioned in WHERE clause is not present.

  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    Perhaps that's because Oracle hasn't taken up AB's suggestion and backported it to 11.2.0.3 yet.

  • ApexBine
    ApexBine Member Posts: 153 Silver Badge

    NOT WORKING IN 11.2.0.3.

    even the columns mentioned in WHERE clause is not present.

    MKJ, are we talking about the first statement? Actually, I would expect that to work in any 11.2 version.

    Is the view sys.all_identifiers there at all?

    Anyway, this example should work (and show some duplicates) if you have the HR schema:

    select d.department_name,

      listagg(e.salary ,', ') within group (order by e.salary) salaries

    from employees e

    join departments d

    on d.department_id = e.department_id

    group by d.department_name

  • MKJ10930279
    MKJ10930279 Member Posts: 244

    MKJ, are we talking about the first statement? Actually, I would expect that to work in any 11.2 version.

    Is the view sys.all_identifiers there at all?

    Anyway, this example should work (and show some duplicates) if you have the HR schema:

    select d.department_name,

      listagg(e.salary ,', ') within group (order by e.salary) salaries

    from employees e

    join departments d

    on d.department_id = e.department_id

    group by d.department_name

    Hi,

    The second statement is throwing error, on DISTINCT, ORA-00907; missing right parenthesis.

    Structure is fine.

  • ApexBine
    ApexBine Member Posts: 153 Silver Badge

    Hi,

    The second statement is throwing error, on DISTINCT, ORA-00907; missing right parenthesis.

    Structure is fine.

    Well, the second example is just a possble syntax for my suggestion. It won't work in a current database.

    FatMartinR
  • MKJ10930279
    MKJ10930279 Member Posts: 244

    Well, the second example is just a possble syntax for my suggestion. It won't work in a current database.

    If that is the case, then its a good idea.

    ApexBine
  • Rafiq D
    Rafiq D Member Posts: 57 Blue Ribbon

    Somehow, I think you can use an outer select using the WITH clause to group the result before using the listagg. This will do the trick

  • Sven W.
    Sven W. Member Posts: 10,534 Gold Crown

    Somehow, I think you can use an outer select using the WITH clause to group the result before using the listagg. This will do the trick

    Yes in general it is possible to do such a distinct by stacking up two listagg functions. But it is clumsy to develop and not so easy to implement when more then 1 column needs to be aggregated.

  • Chris Hunt
    Chris Hunt Member Posts: 2,066 Gold Trophy

    Yes in general it is possible to do such a distinct by stacking up two listagg functions. But it is clumsy to develop and not so easy to implement when more then 1 column needs to be aggregated.

    You don't need two listagg functions, you just need to do a distinct on the underlying table(s). So instead of this:

    select deptno,listagg (sal,', ') within group (order by sal)
    from scott.emp
    group by deptno;
    

    do this

    select deptno,listagg (sal,', ') within group (order by sal)
    from (select distinct deptno,sal from scott.emp)
    group by deptno;
    
  • ApexBine
    ApexBine Member Posts: 153 Silver Badge

    You don't need two listagg functions, you just need to do a distinct on the underlying table(s). So instead of this:

    select deptno,listagg (sal,', ') within group (order by sal)
    from scott.emp
    group by deptno;
    

    do this

    select deptno,listagg (sal,', ') within group (order by sal)
    from (select distinct deptno,sal from scott.emp)
    group by deptno;
    

    Hi @Chris Hunt,

    this is fine as long as I want to concatenate only one column.

    I'm still looking for a solution for concatenating two columns (separately)

    or for using listagg with "over (partition by ...)"