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.
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.
ROW_NUMBER() Function without Partition By clause
Partition by clause is a 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
ROW_NUMBER() Function with Partition By clause
PARTITION BY Clause
When you specify a column or set of columns with PARTITION
BY clause then it will divide the result set into record partitions and
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
