Sunday, October 28, 2012

How to suppress output of duplicate values - Oracle SQL

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

1 comment: