Q

Getting a row number in a group

I need to get a rownumber into the group. For example:

group  value  rownumber
1        5        1
1        6        2
1        3        3
1        8        4
2        4        1
2        3        2
2        6        3
2        8        4
2        9        5

To get the rownumber within a group use the row_number() analytic function. You must tell row_number how to partition (like a group by) and how to order the partition. Here's an example SELECT using the data you provided:

select group, value, row_number() over (partition by group order by group) as rownumber
  from my_table ;
 
Here's another example (with results) using the familiar scott.emp table:
select empno, ename, deptno, sal,
       row_number() over (partition by deptno order by deptno) as srnbr
  from emp ;

     EMPNO ENAME          DEPTNO        SAL      SRNBR
---------- ---------- ---------- ---------- ----------
      7782 CLARK              10       2450          1
      7839 KING               10       5000          2
      7934 MILLER             10       1300          3
      7369 SMITH              20        800          1
      7876 ADAMS              20       1100          2
      7902 FORD               20       3000          3
      7788 SCOTT              20       3000          4
      7566 JONES              20       2975          5
      7499 ALLEN              30       1600          1
      7698 blake              30       2850          2
      7654 MARTIN             30       1250          3
      7900 JAMES              30        950          4
      7844 TURNER             30       1500          5
      7521 WARD               30       1250          6

For More Information


This was first published in November 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close