In this article, we will learn how to use ROW_NUMBER() function with a SQL select query.
The ROW_NUMBER() is a window function that returns a unique sequential integer for each row within a partition of a result set.
It starts with 1 for the first row in each partition and without repeating or skipping numbers in the ranking result of each partition.
Syntax
Select column_name(s),
ROW_NUMBER() OVER([PARTITION BY expression(s)] ORDER BY column(s))
From TABLE_NAME;
Note: The ROW_NUMBER() function must have an OVER clause.
OVER Clause
The OVER clause is used to determine which rows from the query are applied to the function, what order they are evaluated in by that function, and when the function’s calculations should restart.
PARTITION BY Clause
The PARTITION BY is a subclause of the OVER clause. The PARTITION BY clause is used to divide a query’s result set into partitions. The ROW_NUMBER() function is applied to each partition separately and reinitialize the row number for each partition.
Example-1 (with using PARTITION BY)
Select *,
ROW_NUMBER() OVER(PARTITION BY age ORDER BY age) as RowNumber
From StudentInfo;
Output:
The PARTITION BY clause is optional. If you skip it, the ROW_NUMBER() function will treat the whole result set as a single partition.
Example-2 (without using PARTITION BY)
Select *,
ROW_NUMBER() OVER(ORDER BY age desc) as RowNumber
From StudentInfo;
Output:
Also, check How To Get Backup Of SQL DB Using Query
