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.
ExamplesTo 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 ()