Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Oracle Rownum

Posted on 2004-04-18
Medium Priority
5,297 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
Question by:sindhuanand
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
4 Comments

LVL 6

Accepted Solution

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

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

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
###### Suggested Courses
Course of the Month7 days, 3 hours left to enroll

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

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