All Products
Search
Document Center

ROW_NUMBER

Last Updated: Jun 18, 2021

The ROW_NUMBER function assigns a unique number to each row to which the function is applied, regardless of each row in the partition or each row returned by the query. The function returns values starting from 1 based on the ordered sequence of rows specified in the order_by_clause. You can use a subquery to nest another subquery in a query that retrieves ROW_NUMBER in a specified range. This way, you can find a precise subset of rows from the results of the inner query. You can use this function to implement top-n, bottom-n, and inner-n reporting. The query must ensure a deterministic sort order for consistent results.

Syntax

ROW_NUMBER( ) OVER ([ query_partition_clause ] order_by_clause)

Parameters

Parameter

Description

OVER

Uses the OVER clause to define a window for calculation.

Return type

The numeric data is returned.

Examples

Rank the employees by employee output in the unit of department. To create the product table and insert data into the table, execute the following statements:

CREATE TABLE product(name VARCHAR(8), deptno NUMBER, output NUMBER);
INSERT INTO product VALUES('Linda',100,5050);
INSERT INTO product VALUES('Tan',1001,8500);
INSERT INTO product VALUES('Tom',1001,3900);
INSERT INTO product VALUES('John',100,29500);
INSERT INTO product VALUES('Mery',1001,1500);
INSERT INTO product VALUES('Peter',100,1060);
COMMIT;

Execute the following statement:

SELECT name,OUTPUT,deptno,ROW_NUMBER() OVER (partition BY deptno ORDER BY OUTPUT DESC) FROM product;

The following query result is returned:

+-------+--------+--------+------------------------------------------------------+
| NAME  | OUTPUT | DEPTNO | ROW_NUMBER()OVER(PARTITIONBYDEPTNOORDERBYOUTPUTDESC) |
+-------+--------+--------+------------------------------------------------------+
| John  |  29500 |    100 |                                                    1 |
| Linda |   5050 |    100 |                                                    2 |
| Peter |   1060 |    100 |                                                    3 |
| Tan   |   8500 |   1001 |                                                    1 |
| Tom   |   3900 |   1001 |                                                    2 |
| Mery  |   1500 |   1001 |                                                    3 |
+-------+--------+--------+------------------------------------------------------+