Re: Row count in group
Date: 20 Aug 1999 11:51:08 GMT
Message-ID: <7pjffc$s4j$8_at_news.seed.net.tw>
Agi <agichen_at_my-deja.com> wrote in message news:7pgis7$hff$1_at_nnrp1.deja.com...
> Hi,everyone,
> I have a problem about row counter.
> If I have a table likes follows
>
> dept_id emp_id name
> ------- ------ -------
> 101 1 Mary
> 101 12 John
> 101 33 Tom
> 102 24 Jim
> 102 25 Peter
> ......
> 109 300 King
>
> Is it possible to get the result via SQL*PLUS ??
>
>
> dept rowcnt emp_id name
> 101 1 1 Mary
> 2 12 John
> 3 33 Tom
>
> 102 1 24 Jim --row# reset on dept
> 2 25 Peter
> ....
>
> I knew it's easy without row# shown,but how to add row counter ??
>
> Is it possible in sqlplus ??
> Any idea ??
>
> Rgds,
> Agi
It's possible.
See the following sample:
SQL> break on deptno
SQL> select a.deptno, count(*) as rowcnt, a.empno, a.ename
2 from emp a, emp b
3 where a.deptno=b.deptno
4 and a.empno>=b.empno
5 group by a.deptno, a.empno, a.ename;
DEPTNO ROWCNT EMPNO ENAME
--------- --------- --------- ----------
10 1 7782 CLARK 2 7839 KING 3 7934 MILLER 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
14 rows selected. Received on Fri Aug 20 1999 - 13:51:08 CEST