?
Solved

Oracle Rownum

Posted on 2004-04-18
4
Medium Priority
?
5,352 Views
Last Modified: 2009-09-30
i have this query

select deptno,sal,rownum
from
(
select deptno,sum(sal) sal
from emp
group by deptno)

this will give me the rownum....but i want to reset the rownum when the dept changes.How can you do this or is there any other way????
0
Comment
Question by:sindhuanand
2 Comments
 
LVL 6

Accepted Solution

by:
musdu earned 200 total points
ID: 10857124
Hi,

select deptno,sal, rank() over (partition by deptno order by rownum) as seq
from
(
select deptno,sum(sal) sal
from emp
group by deptno)

regards
0
 
LVL 13

Expert Comment

by:riazpk
ID: 10857134
This is Easy:


sect deptno,sal,row_number() over (partition by deptno order by '1')
from
(select deptno,sum(sal) sal
from emp
group by deptno)


Analytics Rock...Analytics Roll


BUT

my question is:

since you are grouping by deptno, so if you want to reset the rownum for each deptno, then each deptno will have rownum 1 eactly...this doesn't makes sense to me. For Example:
  1  select deptno,sal,row_number() over (partition by deptno order by '1')
  2  from
  3  (select deptno,sum(sal) sal
  4  from test.emp
  5* group by deptno)
SQL> /

    DEPTNO        SAL ROW_NUMBER()OVER(PARTITIONBYDE
========== ========== ==============================
        10       8750                              1
        20      10075                              1
        30       9480                              1


However, this may makes sense:

  1  select empno,ename,deptno,sal,row_number() over (partition by deptno order by '1')
  2* from test.emp
SQL> /

     EMPNO ENAME          DEPTNO        SAL ROW_NUMBER()OVER(PARTITIONBYDE
========== ========== ========== ========== ==============================
      7782 CLARK              10       2450                              1
      7839 KING               10       5000                              2
      7934 MILLER             10       1300                              3
      7566 JONES              20       2975                              1
      7902 FORD               20       3000                              2
      7876 ADAMS              20       1100                              3
      7788 SCOTT              20       3000                              4
      7499 ALLEN              30       1680                              1
      7900 JAMES              30        950                              2
      7844 TURNER             30       1500                              3
      7654 MARTIN             30       1250                              4
      7698 BLAKE              30       2850                              5
      7521 WARD               30       1250                              6

Would you please tell us what is reason behind getting such output (as you described) ?
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question