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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.