Re: Row count in group

[Quoted] Though rownum is always related to the main query, not a subquery, you can get messy result. Quick and dirty solution is

select a.dept_id, count(*) rowcnt, a.emp_id from emp a, emp b  where a.dept_id=b.dept_id and
  b.rowid<=a.rowid
 group by a.dept_id, a.emp_id
;

Michel Cadot <micadot_at_francemel.com> wrote in article <7pgvhk$afc$1_at_oceanite.cybercable.fr>...
:
: Paul Dorsey a écrit dans le message ...
: >Just add rownum to the select statement. e.g.
: >
: >select dept_id, rownum, emp_id,name
: >
: >rownum is a reserved word that will do what you want.
: That doesn't work: rownum is not reset for each dept_id.
:
: You can use this:
: break on dept_id
: select a.dept_id, rownum-b.cnt+1 rowcnt, a.emp_id, a.name
: from emp a, (select min(rownum) cnt, dept_id from emp group by
dept_id) b
: where b.dept_id=a.dept_id
: order by a.dept_id, a.emp_id;
: >
: >Agi wrote in message <7pgis7$hff$1_at_nnrp1.deja.com>...
: >>Hi,everyone,
: >> I have a problem about row counter.
[skipped]
: >> 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 ??
  Received on Thu Aug 19 1999 - 20:25:39 CEST