ROW_NUMBER

ROW_NUMBER assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the ORDER BY clause, beginning with 1.

Syntax

ROW_NUMBER () OVER ( [ PARTITION BY expression [, ...] ]

 ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )

Remarks

•

PARTITION BY is optional.

•

ORDER BY is required.

•

The window clause is not allowed.

•

If ROW_NUMBER appears in a subquery, its behavior may not be the same as the Oracle ROWNUM function.

Examples

You want to number each manager’s employees by hire date. Use a query like the following:

SELECT

ROW_NUMBER() OVER (PARTITION BY manager ORDER BY hire_date)

FROM EMPLOYEES

This query first partitions the employees by manager, then orders employees in each partition by hire date, and then applies the ROW_NUMBER function. However, because multiple employees might have been hired on the same date, repeated execution of this query could return a different ordering of same-day employees. To make sure the returned order is consistent, add a second expression to the ORDER BY clause:

SELECT 

ROW_NUMBER() OVER (PARTITION BY manager ORDER BY hire_date, ID)

FROM EMPLOYEES