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?

It is extremely useful, thanks.

You are welcome Dai Juehua!

this is good and thanks for the detail explanation.

Thanks for this explanation. It was very helpful.

Many thanks for the appreciation.

Well done, thank you!

My pleasure