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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: