Row_Number Function With PARTITION BY Clause In SQL Server

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