Row_Number Function With PARTITION BY Clause In SQL Server

Create table Employee  
(  
   EmpID int,  
   EmpName varchar(30),  
   EmpSalary int  
)

The following is the sample data for the employee Table.

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 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