8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
ROW_NUMBER Analytic Function
This article gives and overview of the ROW_NUMBER
analytic function. If you are new to analytic functions you should probably read this introduction to analytic functions first.
Related articles.
- Ranking using RANK, DENSE_RANK, ROW_NUMBER
- Analytic Functions : All Articles
- RANK and DENSE_RANK Analytic Functions
Setup
The examples in this article require the following table.
--DROP TABLE emp PURGE; CREATE TABLE emp ( empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) ); INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20); INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20); INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); COMMIT;
ROW_NUMBER Analytic Function
If you have ever used the ROWNUM
pseudocolumn, you will have an idea what the ROW_NUMBER
analytic function does. It is used to assign a unique number from 1-N to the rows within a partition. At first glance this may seem similar to the RANK and DENSE_RANK analytic functions, but the ROW_NUMBER
function ignores ties and always gives a unique number to each row.
The basic description for the ROW_NUMBER
analytic function is shown below. The analytic clause is described in more detail here.
ROW_NUMBER() OVER ([ query_partition_clause ] order_by_clause)
The ROW_NUMBER
analytic function is order-sensitive and produces an error if you attempt to use it without an ORDER BY
in the analytic clause. Unlike some other analytic functions, it doesn't support the windowing clause. Omitting a partitioning clause from the OVER
clause means the whole result set is treated as a single partition. In the following example we assign a unique row number to each employee based on their salary (lowest to highest). The example also includes RANK
and DENSE_RANK
to show the difference in how ties are handled.
SELECT empno, ename, deptno, sal, ROW_NUMBER() OVER (ORDER BY sal) AS row_num, RANK() OVER (ORDER BY sal) AS row_rank, DENSE_RANK() OVER (ORDER BY sal) AS row_dense_rank FROM emp; EMPNO ENAME DEPTNO SAL ROW_NUM ROW_RANK ROW_DENSE_RANK ---------- ---------- ---------- ---------- ---------- ---------- -------------- 7369 SMITH 20 800 1 1 1 7900 JAMES 30 950 2 2 2 7876 ADAMS 20 1100 3 3 3 7521 WARD 30 1250 4 4 4 7654 MARTIN 30 1250 5 4 4 7934 MILLER 10 1300 6 6 5 7844 TURNER 30 1500 7 7 6 7499 ALLEN 30 1600 8 8 7 7782 CLARK 10 2450 9 9 8 7698 BLAKE 30 2850 10 10 9 7566 JONES 20 2975 11 11 10 7788 SCOTT 20 3000 12 12 11 7902 FORD 20 3000 13 12 11 7839 KING 10 5000 14 14 12 SQL>
Adding the partitioning clause allows us to assign the row number within a partition. In the following example we assign the row number within the department, based on highest to lowest salary.
SELECT empno, ename, deptno, sal, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS row_num FROM emp; EMPNO ENAME DEPTNO SAL ROW_NUM ---------- ---------- ---------- ---------- ---------- 7839 KING 10 5000 1 7782 CLARK 10 2450 2 7934 MILLER 10 1300 3 7788 SCOTT 20 3000 1 7902 FORD 20 3000 2 7566 JONES 20 2975 3 7876 ADAMS 20 1100 4 7369 SMITH 20 800 5 7698 BLAKE 30 2850 1 7499 ALLEN 30 1600 2 7844 TURNER 30 1500 3 7654 MARTIN 30 1250 4 7521 WARD 30 1250 5 7900 JAMES 30 950 6 SQL>
This allows us to write Top-N queries at the partition level. The following example brings back the best paid person in each department, ignoring ties.
SELECT * FROM (SELECT empno, ename, deptno, sal, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS row_num FROM emp) WHERE row_num = 1; EMPNO ENAME DEPTNO SAL ROW_NUM ---------- ---------- ---------- ---------- ---------- 7839 KING 10 5000 1 7788 SCOTT 20 3000 1 7698 BLAKE 30 2850 1 SQL>
Quick Links
The "*" indicates the function supports the full analytic syntax, including the windowing clause.
For more information see:
- ROW_NUMBER
- Ranking using RANK, DENSE_RANK, ROW_NUMBER
- Analytic Functions : All Articles
- RANK and DENSE_RANK Analytic Functions
Hope this helps. Regards Tim...