19C LISTAGG DISTINCT

View All Scripts Login to Run Script

Script Name 19C LISTAGG DISTINCT
Description 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.
Area Oracle 19c
Contributor Oracle
Created Tuesday January 15, 2019

Statement 1

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.

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

Statement 2

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.

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

Statement 3

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

select d.dname,   
       listagg (DISTINCT 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, CLERK, MANAGER
SALES CLERK, MANAGER, SALESMAN

3 rows selected.

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

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database