Tip of the Week
Tip for Week of November 29, 2004

Suppress Repeating Groups Using RANK (not ROWNUM)

This tip comes from Mohammad Anwar, Oracle Developer, Tuskerdirect Ltd, in London, United Kingdom.

This tip achieves the same output as the Tip for Week of August 23, 2004. In that tip, the author uses ROWNUM and inline views, whereas I use the analytic function RANK() to do the job.

Here is the complete script:

create table emp (
empno number(3),
dept number(3),
name varchar2(15)
);

insert into emp values(1,100,'Tracey Turner');
insert into emp values(10,100,'Peter Watson');
insert into emp values(99,200,'David Truman');
insert into emp values(80,200,'William Fender');
insert into emp values(2,300,'Sara Joshua');
insert into emp values(3,200,'Joseph Derner');
insert into emp values(60,100,'Sandra Harper');

Original Statement:

select decode(rownum-min_sno,0,a.dept,null) dept,
decode(rownum-min_sno,0,1,rownum+1-min_sno) sno,
a.empno,name
from (select * from emp order by dept,empno ) a,
(select dept,min(rownum) min_sno
from ( select * from emp order by dept,empno )
group by dept ) b
where a.dept=b.dept
/

Modified Statement using RANK():

select case when rank() over (partition by dept order by empno) = 1 then dept else null end as dept,
rank() over (partition by dept order by empno) as sno,
empno,
name
from emp;
/

Output:

DEPT       SNO        EMPNO      NAME
---------- ---------- ---------- ---------------
100        1          1          Tracey Turner
           2          10         Peter Watson
           3          60         Sandra Harper
200        1          3          Joseph Derner
           2          80         William Fender
           3          99         David Truman
300        1          2          Sara Joshua