Skip navigation

LISTAGG with DISTINCT option

score 780
You have not voted. Delivered

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, 2850


SQL> 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

Comments

Vote history