Ask the Expert

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

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top