In this article we will learn how to select Row Number in Select query of SQL Server and what are other usage of ROW_NUMBER Function.
ROW_NUMBER function is used to return sequential number in
SELECT query along with other columns. Microsoft introduced
ROW_NUMBER function in SQL Server 2005. It is used with
ORDER BY clause and
PARTITION BY Clause.
ORDER BY Clause is required but
PARTITION BY Clause is optional.
When data is partitioned, row number is reset to number 1 whenever partition is changed.
Syntax for ROW_NUMBER function.
ROW_NUMBER() over (PARTITION BY Column1, ORDER BY Column1, Column2)
Lets Understand with an Example
Here is the EmployeeMst Table with columns Name, Department, Age.
Now, along with these three columns we will return SrNo column using
ROW_NUMBER function in the Select Query.
As we can notice that
ROW_NUMBER() does not accept any parameters, but have to mention
ORDER BY inside Over() clause.
ORDER BY used with the
ROW_NUMBER function sorts the specified column and generates the sequence based on that column.
PARTITION BY used with
ROW_NUMBER function partitions the result over specified columns and resets row number to 1 when value changed in partitioned.
PARTITION BY clause
Let’s use ROW_NUMBER with PARTITION BY clause over Department column and see what happens. Look at this image.
In this example we can notice that returned data sorted by Department and SrNo is reset to 1 wherever Department is changed in the result set. This is how
PARTITION BY separates the row numbers
PARTITION BY is also used to find duplicate records and delete the duplicated records
See Also :