LISTAGG-DISTINCT/ORDER BY

View All Public Scripts Login to Run Script

Script Name LISTAGG-DISTINCT/ORDER BY
Visibility Unlisted - anyone with the share link can access
Description Shows how the new LISTAGG-DISTINCT with a non-matching ORDER BY still does the right thing.
Contributor frieder.schenke@ottogroup.com
Created Wednesday February 06, 2019

Statement 1

select d.dname,    
       listagg (DISTINCT e.job,', ' on overflow truncate with count)      
                within group (order by e.ename) jobs   
  from scott.dept d, scott.emp e      
 where d.deptno = e.deptno      
 group by d.dname


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

3 rows selected.

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

Statement 2

 select * 
 from scott.dept d, scott.emp e      
 where d.deptno = e.deptno      
 and d.deptno = 20 
 order by ename


DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 23-MAY-87 1100  - 20
20 RESEARCH DALLAS 7902 FORD ANALYST 7566 03-DEC-81 3000  - 20
20 RESEARCH DALLAS 7566 JONES MANAGER 7839 02-APR-81 2975  - 20
20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 19-APR-87 3000  - 20
20 RESEARCH DALLAS 7369 SMITH CLERK 7902 17-DEC-80 800  - 20

5 rows selected.

DEPTNO	DNAME	LOC	EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
20	RESEARCH	DALLAS	7876	ADAMS	CLERK	7788	23-MAY-87	1100	-	20
20	RESEARCH	DALLAS	7902	FORD	ANALYST	7566	03-DEC-81	3000	-	20
20	RESEARCH	DALLAS	7566	JONES	MANAGER	7839	02-APR-81	2975	-	20
20	RESEARCH	DALLAS	7788	SCOTT	ANALYST	7566	19-APR-87	3000	-	20
20	RESEARCH	DALLAS	7369	SMITH	CLERK	7902	17-DEC-80	800	-	20

Additional Information

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