ROW_NUMBER

Purpose

This function returns the number of a row in an ordered partition.

Syntax

row_number::=

Usage Notes

  • ROW_NUMBER can only be used as an analytic function, (i.e., in combination with over_clause). For more information, refer to Analytic Functions section.
  • The over_clause must not contain a window_frame_clause.
  • The value is non-deterministic with rows of equal ranking.

Example

Analytic Function

Copy 
SELECT 
    id, department, hire_date, 
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY hire_date) ROW_NUMBER 
FROM employee_table ORDER BY department, hire_date;
Result
ID DEPARTMENT HIRE_DATE ROW_NUMBER
2005 ACCOUNTS 2013-01-01 1
2003 ACCOUNTS 2015-07-01 2
2002 ACCOUNTS 2017-01-01 3
2004 ACCOUNTS 2017-01-01 4
2001 ACCOUNTS 2018-07-01 5
1003 HR 2014-01-01 1
1002 HR 2016-01-01 2
1004 HR 2016-01-01 3
1001 HR 2018-01-01 4