Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 535.8K On-Premises Infrastructure
- 138.1K Analytics Software
- 38.6K Application Development Software
- 5.6K Cloud Platform
- 109.3K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
LISTAGG with DISTINCT option
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
Comments
-
NOT WORKING IN 11.2.0.3.
even the columns mentioned in WHERE clause is not present.
-
Perhaps that's because Oracle hasn't taken up AB's suggestion and backported it to 11.2.0.3 yet.
-
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
-
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.
-
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.
-
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.
-
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
-
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.
-
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;
-
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 ...)"