Re: Row count in group

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.
>> 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
>>
>>
>>
>>Sent via Deja.com http://www.deja.com/
>>Share what you know. Learn what you don't.
>
>
Received on Thu Aug 19 1999 - 15:06:18 CEST