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