19C LISTAGG DISTINCT

Overview

The LISTAGG aggregate function now supports duplicate elimination by using the new DISTINCT keyword. The LISTAGG aggregate function orders the rows for each group in a query according to the ORDER BY expression and then concatenates the values into a single string. With the new DISTINCT keyword, duplicate values can be removed from the specified expression before concatenation into a single string. This removes the need to create complex query processing to find the distinct values prior to using the aggregate LISTAGG function. With the DISTINCT option, the processing to remove duplicate values can be done directly within the LISTAGG function. The result is simpler, faster, more efficient SQL.

Technologies and Tools Used

The following technologies have been used to achieve this

Oracle SQL,PL/SQL

Use Case

Notice the repeated results.

select d.dname,

listagg (e.job,’, ‘ on overflow truncate with count)

within group (order by e.job) jobs

from scott.dept d, scott.emp e

where d.deptno = e.deptno

group by d.dname

DNAME	JOBS
ACCOUNTING	CLERK, MANAGER, PRESIDENT
RESEARCH	ANALYST, ANALYST, CLERK, CLERK, MANAGER
SALES	CLERK, MANAGER, SALESMAN, SALESMAN, SALESMAN, SALESMAN

3 rows selected.

Steps with Screenshot

To remove the duplicates, prior to 19c, you would use a nested select to get just the unique jobs for the LISTAGG function.

SELECT d.dname,

(select LISTAGG(job,’, ‘ ON OVERFLOW TRUNCATE WITH COUNT)

WITHIN GROUP (ORDER BY job)

from (select unique job job

from scott.emp e

where d.deptno = e.deptno)) jobs

FROM scott.dept d

DNAME	JOBS
ACCOUNTING	CLERK, MANAGER, PRESIDENT
RESEARCH	ANALYST, CLERK, MANAGER
SALES	CLERK, MANAGER, SALESMAN
OPERATIONS	–

4 rows selected.

With 19c, you can now just use DISTINCT within your LISTAGG to remove any repeated values.