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


Dig Deeper on Oracle development languages

Data Management
Business Analytics
SearchSAP
TheServerSide.com
Data Center
Content Management
HRSoftware
Close