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