8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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.

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.

AVG * CLUSTER_DETAILS CLUSTER_DISTANCE CLUSTER_ID CLUSTER_PROBABILITY
CLUSTER_SET CORR * COUNT * COVAR_POP * COVAR_SAMP *
CUME_DIST DENSE_RANK FEATURE_DETAILS FEATURE_ID FEATURE_SET
FEATURE_VALUE FIRST FIRST_VALUE * LAG LAST
LAST_VALUE * LEAD LISTAGG MATCH_RECOGNIZE MAX *
MEDIAN MIN * NTH_VALUE * NTILE PERCENT_RANK
PERCENTILE_CONT PERCENTILE_DISC PREDICTION PREDICTION_COST PREDICTION_DETAILS
PREDICTION_PROBABILITY PREDICTION_SET RANK RATIO_TO_REPORT REGR_ (Linear Regression) Functions *
ROW_NUMBER STDDEV * STDDEV_POP * STDDEV_SAMP * SUM *
VAR_POP * VAR_SAMP * VARIANCE * String Aggregation Top-N Queries

For more information see:

Hope this helps. Regards Tim...

Back to the Top.