ROW_NUMBER function

The ROW_NUMBER function returns the row number over a named or unnamed window specification.

The ROW_NUMBER function does not take any arguments, and for each row over the window it returns an ever increasing BIGINT. It is normally used to limit the number of rows returned for a query. A result offset or fetch first clause can be a more efficient way to perform this task.

The data type of the returned value is BIGINT.

Syntax

ROW_NUMBER ( ) OVER [ windowSpecification | windowName ]

Currently, the only valid windowSpecification is an empty pair of parentheses (()), which indicates that the function is evaluated over the entire result set.

If you choose to use a WINDOW clause in a selectExpression to specify a window, you must specify a windowName to refer to it.

Examples

To limit the number of rows returned from a query to the first 10 rows of table T, use the following query:

SELECT * FROM (
   SELECT 
     ROW_NUMBER() OVER () AS R, 
     T.* 
   FROM T
) AS TR 
   WHERE R <= 10;

To display the result of a query using a window name in a WINDOW clause:

SELECT ROW_NUMBER() OVER R, 
    B, 
    SUM(A) 
FROM T5 GROUP BY B WINDOW R AS ()