|
Tip of the Week
Tip for Week of August 23, 2004
Reset ROWNUM and Suppress Repeating Groups
This tip comes from
Satyanarayana Josyula, Sr. Oracle DBA at Nexus Energy Software, in Reston, Virginia.
Note: This tip was written for use with Oracle9i.
You can reset the serial numbers after each group break using
a single query--without using any utilities. This technique uses the ROWNUM
returned by Oracle and resets the serial number at the
beginning of each group. Also this query shows how to suppress the repeating
group values in the result set.
Let us look at this simple example:
Table test
Name Null? Type
----------------------------------------- -------- ---------------
EMPNO NUMBER(3)
DEPT NUMBER(3)
NAME VARCHAR2(15)
SQL> SELECT * FROM emp;
EMPNO DEPT NAME
---------- ---------- ---------------
1 100 Tracey Turner
10 100 Peter Watson
99 200 David Truman
80 200 William Fender
2 300 Sara Joshua
3 200 Joseph Derner
60 100 Sandra Harper
Suppose you want output in the form:
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
You can use the following query:
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
/
Note: The following revision of this query works with the standard
SCOTT/TIGER sample schema:
SELECT DECODE(ROWNUM-min_sno,0,a.deptno,NULL) dept,DECODE(ROWNUM-min_sno,0,1,rownum+1-min_sno) sno,a.empno,ename FROM
(SELECT * FROM emp ORDER BY deptno,empno ) a,
(SELECT deptno,MIN(rownum) min_sno FROM (SELECT * FROM emp ORDER BY deptno,empno)GROUP BY deptno) b
WHERE a.deptno=b.deptno
/
|
Bookmark
