Usages of ROW_NUMBER() in MSSQL SERVER

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

FROM SALES;

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
FROM (
SELECT CustomerID, ProductID, PurchasedDate, ROW_NUMBER() OVER(
PARTITION BY CustomerID, ProductID ORDER BY PurchasedDate ASC) AS RowNO

FROM SALES
) 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?

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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