ROW_NUMBER in SQL Server with PARTITION BY

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.

Introduction

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.

Select Query

Now, along with these three columns we will return SrNo column using ROW_NUMBER function in the Select Query.

RowNumber with Order By
ROW_NUMBER example without PARTITION BY clause

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.

Example with PARTITION BY clause

Let’s use ROW_NUMBER with PARTITION BY clause over Department column and see what happens. Look at this image.

ROW_NUMBER with Partition By Clause
ROW_NUMBER example with PARTITION BY Clause

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

ROW_NUMBER with PARTITION BY is also used to find duplicate records and delete the duplicated records


See Also :

Using ROW_NUMBER to find Duplicate Records and Delete Duplicate Records from SQL Server Table


Click here to read more articles related to SQL Server

7 thoughts on “ROW_NUMBER in SQL Server with PARTITION BY

Leave a Reply

Your email address will not be published. Required fields are marked *