How To Reset Rownum

[mm_taha]

<div align="left"></div>هذا المثال جيد جدا وجدته من خلال البحث عن طريقة عمل ريست لل رو نم
واردت ان يستفاد منه الجميع


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:

 
[sql]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[/sql]

و هذا المثال لكيفية عمل ما سبق علي السكيما SCOTT/TIGER التقليدية

SQL> SELECT *  FROM  EMP;

 EMPNO ENAME	  JOB			  MGR HIREDATE		  SAL	   COMM	 DEPTNO								  
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------								  
  7369 SMITH	  CLERK		   7902 17-12-1980		800					20								  
  7499 ALLEN	  SALESMAN		7698 20-02-1981	   1600		300		 30								  
  7521 WARD	   SALESMAN		7698 22-02-1981	   1250		500		 30								  
  7566 JONES	  MANAGER		 7839 02-04-1981	   2975					20								  
  7654 MARTIN	 SALESMAN		7698 28-09-1981	   1250	   1400		 30								  
  7698 BLAKE	  MANAGER		 7839 01-05-1981	   2850					30								  
  7788 SCOTT	  ANALYST		 7566 19-04-1987	   3000					20								  
  7844 TURNER	 SALESMAN		7698 08-09-1981	   1500		  0		 30								  
  7876 ADAMS	  CLERK		   7788 23-05-1987	   1100					20								  
  7900 JAMES	  CLERK		   7698 03-12-1981		950					30								  
  7902 FORD	   ANALYST		 7566 03-12-1981	   3000					20								  

تم اختيار 11 صف

و لاظهار الموظفين في شكل تقرير GROUP_LEFT باستخدام الROWNUM مع عمل RESET له عند كل إدارة يستخدم الQUERY الأتي

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

حيث ستظهر النتيجة بالشكل التالي

SQL> SELECT DECODE (ROWNUM - min_sno, 0, a.deptno, NULL) dept,
 2		 DECODE (ROWNUM - min_sno, 0, 1, ROWNUM + 1 - min_sno) sno, a.empno,
 3		 ename
 4	FROM (SELECT   *
 5			  FROM emp
 6		  ORDER BY deptno, empno) a,
 7		 (SELECT   deptno, MIN (ROWNUM) min_sno
 8			  FROM (SELECT   *
 9						FROM emp
10					ORDER BY deptno, empno)
11		  GROUP BY deptno) b
12   WHERE a.deptno = b.deptno;

  DEPT		SNO	  EMPNO ENAME																				  
---------- ---------- ---------- ----------																			 
	20		  1	   7369 SMITH																				  
				2	   7566 JONES																				  
				3	   7788 SCOTT																				  
				4	   7876 ADAMS																				  
				5	   7902 FORD																				   
	30		  1	   7499 ALLEN																				  
				2	   7521 WARD																				   
				3	   7654 MARTIN																				 
				4	   7698 BLAKE																				  
				5	   7844 TURNER																				 
				6	   7900 JAMES																				  

تم اختيار 11 صف

تم تعديل المثال و تضمينه مثال يعمل علي SCOTT/TIGER
و إضافة الSELECT في ملف نصي بالمرفقات
مع تحيات إدارة المنتدي

GROUP_LEFT_SELECT.txt

[mm_taha]

و هذا هو الربط

http://www.oracle.com/technology/oramag/co...004/082304.html

[wlahmad]

نور الله دربك
وفرج كربك
وازال همك
وبلغك مرادك