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 last published in November 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close