Row_Number function in SQL
In this blog, you will see Row_Number function without Partition By or with Partition By clause.
Creating a table in SQL Server
Now we create a table named employee.
Create table Employee
(
EmpID int,
EmpName varchar(30),
EmpSalary int
)
The following is the sample data for the employee Table.
![SQL ROW_NUMBER() Function]()
SQL ROW_NUMBER() Function
The Row_Numaber function is an important function when you do paging in SQL Server. The Row_Number function is used to provide consecutive numbering of the rows in the result by the order selected in the OVER clause for each partition specified in the OVER clause. It will assign the value 1 for the first row and increase the number of the subsequent rows.
Syntax
ROW_NUMBER ()
OVER ([PARTITION BY value_exp, ... [ n ]] order_by_clause)
OVER - Specify the order of the rows.
ORDER BY - Provide sort order for the records.
The ROW_NUMBER function enumerates the rows in the sort order defined in the over clause.
SQL ROW_NUMBER() Function without Partition By clause
Partition by clause is an optional part of Row_Number function and if you don't use it all the records of the result-set will be considered as a part of single record group or a single partition and then ranking functions are applied.
Example
SELECT *, ROW_NUMBER() OVER(ORDER BY EmpName) AS Row_Number
FROM Employee
Output
![SQL ROW_NUMBER() Function without Partition By clause]()
SQL ROW_NUMBER() Function with Partition By clause
When you specify a column or set of columns with the PARTITION BY clause, then it will divide the result set into record partitions. Then, finally ranking functions are applied to each record partition separately, and the rank will restart from 1 for each record partition separately.
Example
SELECT *, ROW_NUMBER() OVER(Partition by EmpName ORDER BY EmpName) AS Row_Number from Employee
Output
![SQL ROW_NUMBER() Function with Partition By clause]()