SQL> select deptno, ename 2 from emp 3 order by deptno 4 ; DEPTNO ENAME ---------- ---------- 10 CLARK 10 KING 10 MILLER 20 JONES 20 FORD 20 ADAMS 20 SMITH 20 SCOTT 30 WARD 30 TURNER 30 ALLEN 30 JAMES 30 BLAKE 30 MARTIN 14 rows selected. SQL> -- We want to produce report with only new values of DEPTNO column, SQL> -- without repetition. SQL*Plus provides BREAK ON feature. SQL> break on deptno SQL> select deptno, ename 2 from emp 3 order by deptno 4 ; DEPTNO ENAME ---------- ---------- 10 CLARK KING MILLER 20 JONES FORD ADAMS SMITH SCOTT 30 WARD TURNER ALLEN JAMES BLAKE MARTIN 14 rows selected. SQL> clear breaks breaks cleared SQL> -- Without any help from SQL*Plus I tried two approaches. SQL> -- Both with Oracle SQL analytic functions. SQL> select decode ( 2 row_number() over (partition by deptno order by ename) 3 ,1,deptno 4 ) AS DEPTNO 5 ,ename 6 from emp 7 order by emp.deptno 8 ; DEPTNO ENAME ---------- ---------- 10 CLARK KING MILLER 20 ADAMS FORD JONES SCOTT SMITH 30 ALLEN BLAKE JAMES MARTIN TURNER WARD 14 rows selected. SQL> -- It is mandatory to use name of the table in the ORDER BY clause. SQL> -- Next approach - to use LAG function to compare current SQL> -- value of DEPTNO column with previous. If it is the same SQL> -- then there is no output (null). SQL> SQL> select decode ( 2 lag(deptno) over (order by deptno) 3 ,deptno,null,deptno 4 ) as deptno 5 ,ename 6 from emp 7 order by emp.deptno 8 ; DEPTNO ENAME ---------- ---------- 10 CLARK KING MILLER 20 ADAMS FORD JONES SCOTT SMITH 30 ALLEN BLAKE JAMES MARTIN TURNER WARD 14 rows selected. SQL> -- Use TO_NUMBER function or another way to adjust the size SQL> -- of DEPTNO column
Sunday, October 28, 2012
How to suppress output of duplicate values - Oracle SQL
Subscribe to:
Post Comments (Atom)
This is the perfect answer to my problem. THANK YOU.
ReplyDelete