Recipe12.5.Suppressing Repeating Values from a Result Set

Recipe 12.5. Suppressing Repeating Values from a Result Set

Problem

You are generating a report, and, when two rows have the same value in a column, you wish to display that value only once. For example, you want to return DEPTNO and ENAME from table EMP, you wish to group all rows for each DEPTNO, and you wish to display each DEPTNO only one time. You want to return the following result set:

 DEPTNO ENAME ------ ---------     10 CLARK        KING        MILLER     20 SMITH        ADAMS        FORD        SCOTT        JONES     30 ALLEN        BLAKE        MARTIN        JAMES        TURNER        WARD 

Solution

This is a simple formatting problem that is easily solved by the window function LAG OVER provided by Oracle. There are other methods such as scalar subqueries and other window functions that you can use (and that you'll have to use for non-Oracle platforms), but LAG OVER is most convenient and appropriate here.

DB2 and SQL Server

You can use the window function MIN OVER to find the smallest EMPNO for each DEPTNO. Then use a CASE expression to "white out" the rows that do not have this EMPNO:

  1 select case when empno=min_empno  2             then deptno else null  3        end deptno,  4        ename  5   from (  6 select deptno,  7        min(empno)over(partition by deptno) min_empno,  8        empno,  9        ename 10   from emp 11        ) x 

Oracle

Use the window function LAG OVER to access prior rows relative to the current row, to find the first DEPTNO for each partition:

 1 select to_number( 2           decode(lag(deptno)over(order by deptno), 3                 deptno,null,deptno) 4        ) deptno, ename 5   from emp 

PostgreSQL and MySQL

This recipe highlights the use of window functions for easily accessing rows around your current row. At the time of this writing, these vendors do not support window functions.

Discussion

DB2 and SQL Server

The first step is to use the window function MIN OVER to find the lowest EMPNO in each DEPTNO:

  select deptno,        min(empno)over(partition by deptno) min_empno,        empno,        ename   from emp DEPTNO  MIN_EMPNO      EMPNO ENAME ------ ---------- ---------- ----------     10       7782       7782 CLARK     10       7782       7839 KING     10       7782       7934 MILLER     20       7369       7369 SMITH     20       7369       7876 ADAMS     20       7369       7902 FORD     20       7369       7788 SCOTT     20       7369       7566 JONES     30       7499       7499 ALLEN     30       7499       7698 BLAKE     30       7499       7654 MARTIN     30       7499       7900 JAMES     30       7499       7844 TURNER     30       7499       7521 WARD 

The next and last step is to use a CASE expression to suppress the repeated display of DEPTNO. If an employee's EMPNO matches MIN_EMPNO, return DEPTNO, otherwise return NULL:

  select case when empno=min_empno             then deptno else null        end deptno,        ename   from ( Select deptno,        min(empno)over(partition by deptno) min_empno,        empno,        ename   from emp        ) x DEPTNO ENAME ------ ----------     10 CLARK        KING        MILLER     20 SMITH        ADAMS        FORD        SCOTT        JONES     30 ALLEN        BLAKE        MARTIN        JAMES        TURNER        WARD 

Oracle

The first step is to use the window function LAG OVER to return the prior DEPTNO for each row:

 Select lag(deptno)over(order by deptno) lag_deptno,        deptno,        ename   from emp LAG_DEPTNO     DEPTNO ENAME ---------- ---------- ----------                    10 CLARK         10         10 KING         10         10 MILLER         10         20 SMITH         20         20 ADAMS         20         20 FORD         20         20 SCOTT         20         20 JONES         20         30 ALLEN         30         30 BLAKE         30         30 MARTIN         30         30 JAMES         30         30 TURNER         30         30 WARD 

If you eyeball the result set above, you can easily see where DEPTNO matches LAG_ DEPTNO. For those rows, you want to set DEPTNO to NULL. Do that by using DECODE (TO_NUMBER is included to cast DEPTNO as a number):

  select to_number(            decode(lag(deptno)over(order by deptno),                  deptno,null,deptno)         ) deptno, ename   from emp DEPTNO ENAME ------ ----------     10 CLARK        KING        MILLER     20 SMITH        ADAMS        FORD        SCOTT        JONES     30 ALLEN        BLAKE        MARTIN        JAMES        TURNER        WARD