A very important feature of SQL Server is the usages of ROW_NUMBER() function. It gives sequential row number[starts from 1] over duplicate touple (a set of column[s]) and the order you set. The sequence of using ROW_NUMBER() in your SELECT SQL is:

ROW_NUMBER ( ) OVER ( [ partition_by_clause ] order_by_clause )

The PARTITION BY clause determines the duplicate touples on which you
want the sequential row number and ORDER BY clause determines the
criteria to set row number.
Lets see how it works and how it helps us….

In a simple way consider you have a table SALES like

CustomerID ProductID PurchasedDate
C000001 P000001 2010-02-01
C000002 P000001 2010-02-01
C000002 P000001 2010-02-02
C000001 P000001 2010-02-02
P000002 2010-02-03
C000001 P000001 2010-02-03
C000002 P000002 2010-02-04
C000002 P000001 2010-02-04
C000001 P000001 2010-02-04

Now if you execute the following SQL:

SELECT CustomerID, ProductID, PurchasedDate, ROW_NUMBER() OVER (

PARTITION BY CustomerID, ProductID ORDER BY PurchasedDate DESC) AS RowNo


Then the output will be

CustomerID ProductID PurchasedDate RowNo
C000001 P000001 2010-02-04 1
C000001 P000001 2010-02-03 2
C000001 P000001 2010-02-02 3
C000001 P000001 2010-02-01 4
C000001 P000002 2010-02-03 1
C000002 P000001 2010-02-04 1
C000002 P000001 2010-02-02 2
C000002 P000001 2010-02-01 3
C000002 P000002 2010-02-04 1

Explanation of the output:
In the SALES table there has total four distinct CustomerID-ProductID combination. In the SELECT statement this distinct combination is gained by partitioning the column with CustomerID and ProductID columns. Then the partitioned columns are given row number based on PurchasedDate column. In this case the order is DESC.

So the combination C000001-P000001 has 4 rows with dates ‘2010-02-01’ to ‘2010-02-04’. So the ROW_NUMBER()[here the alias RowNo] of row C000001-P000001-2010-02-04 is 1 and then C000001-P000001-2010-02-01 is 4. Similarly the combination C000002-P000001 has three rows with dates ‘2010-02-01’, ‘2010-02-02’ and ‘2010-02-04’. So RowNo for the combination C000002-P000001-2010-02-04 is 1, C000002-P000001-2010-02-02 is 2 and C000002-P000001-2010-02-01 is 3. Here if you use the ASC as ORDER BY then the RowNo would be reverse than it is shown.

Now if you carefully see the second table then imagine how easy it is for you to select distinct CustomerID-ProductID based on most or least or Nth recent sales.

For example you now want to know the 3rd recent sales date of every CustomerID-ProductID combination. So your SQL will look like:

SELECT temp.CustomerID, temp.ProductID, temp.PurchasedDate
SELECT CustomerID, ProductID, PurchasedDate, ROW_NUMBER() OVER(
PARTITION BY CustomerID, ProductID ORDER BY PurchasedDate ASC) AS RowNO

) AS temp
WHERE temp.RowNo = 3;
The output is:

CustomerID ProductID PurchasedDate
C000001 P000001 2010-02-03
C000002 P000001 2010-02-04

Isn’t it very useful feature?


7 thoughts on “Usages of ROW_NUMBER() in MSSQL SERVER

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s