Row Counter in Select statement [message #9275] |
Thu, 30 October 2003 06:25 |
Rob
Messages: 70 Registered: January 2000
|
Member |
|
|
Dear board,
Is there a way to include a cumulative counter in a select statement? So that the resultset contains a column starting with 1, 2, 3...n. Counting the rows as the select executes? (I can't use a cursor or stored proc for office political reasons, stupid I know!)
Many, many thanks,
Rob
|
|
|
Re: Row Counter in Select statement [message #9278 is a reply to message #9275] |
Thu, 30 October 2003 07:38 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
Have you tried the ROWNUM pseudocolumn?SQL> SELECT ROWNUM
2 , e.*
3 FROM emp e
4 /
ROWNUM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
1 7369 SMITH CLERK 7902 12/17/1980 800 20
2 7499 ALLEN SALESMAN 7698 2/20/1981 1600 300 30
3 7521 WARD SALESMAN 7698 2/22/1981 1250 500 30
4 7566 JONES MANAGER 7839 4/2/1981 2975 20
5 7654 MARTIN SALESMAN 7698 9/28/1981 1250 1400 30
6 7698 BLAKE MANAGER 7839 5/1/1981 2850 30
7 7782 CLARK MANAGER 7839 6/9/1981 2450 10
8 7788 SCOTT ANALYST 7566 12/9/1982 3000 20
9 7839 KING PRESIDENT 11/17/1981 5000 10
10 7844 TURNER SALESMAN 7698 9/8/1981 1500 0 30
11 7876 ADAMS CLERK 7788 1/12/1983 1100 20
12 7900 JAMES CLERK 7698 12/3/1981 950 30
13 7902 FORD ANALYST 7566 12/3/1981 3000 20
14 7934 MILLER CLERK 7782 1/23/1982 1300 10
14 rows selected.
SQL> For more on what ROWNUM is (and what it is not), see this "Ask Tom" discussion.
HTH,
Art.
|
|
|
Re: Row Counter in Select statement [message #9279 is a reply to message #9278] |
Thu, 30 October 2003 08:34 |
Rob
Messages: 70 Registered: January 2000
|
Member |
|
|
Thanks Art,
Further to that, is there anyway to re-set the rownum?
Say for example that the EMPNO column contains two consecutive rows of 7499, could the row counter be '1' for the first instance, then '2' for the second. Some sort of (1 of 2) counter?
Thanks again,
Rob
|
|
|
Re: Row Counter in Select statement [message #9280 is a reply to message #9279] |
Thu, 30 October 2003 08:58 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
Rob,
Try the ROW_NUMBER analytic function:SQL> SELECT e.empno
2 , e.ename
3 , e.job
4 , e.mgr
5 , e.hiredate
6 , e.sal
7 , e.comm
8 , e.deptno
9 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY hiredate) rn
10 FROM emp e
11 ORDER BY e.deptno
12 , e.hiredate
13 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 6/9/1981 2450 10 1
7839 KING PRESIDENT 11/17/1981 5000 10 2
7934 MILLER CLERK 7782 1/23/1982 1300 10 3
7369 SMITH CLERK 7902 12/17/1980 800 20 1
7566 JONES MANAGER 7839 4/2/1981 2975 20 2
7902 FORD ANALYST 7566 12/3/1981 3000 20 3
7788 SCOTT ANALYST 7566 12/9/1982 3000 20 4
7876 ADAMS CLERK 7788 1/12/1983 1100 20 5
7499 ALLEN SALESMAN 7698 2/20/1981 1600 300 30 1
7521 WARD SALESMAN 7698 2/22/1981 1250 500 30 2
7698 BLAKE MANAGER 7839 5/1/1981 2850 30 3
7844 TURNER SALESMAN 7698 9/8/1981 1500 0 30 4
7654 MARTIN SALESMAN 7698 9/28/1981 1250 1400 30 5
7900 JAMES CLERK 7698 12/3/1981 950 30 6
14 rows selected.
SQL> HTH,
A.
|
|
|
|