The ranking functions can also combine with windowing functions. A windowing function will divide a resultset into equal partitions based on the values of your PARTITION BY statement in conjunction with the OVER clause in your ranking function. It is like applying a GROUP BY to your ranking function-you get a separate ranking for each partition. The example below uses ROW_NUMBER with PARTITION BY to count the number of orders by order date by salesperson. We do this with a PARTITION BY SalesPersonID OVER OrderDate. This can be used with any of the four ranking functions.

Select SalesOrderID, SalesPersonID, OrderDate,

Row_NUMBER() Over (Partition By SalesPersonID Order By OrderDate) as OrderRank

From Sales.SalesOrderHeader

Where SalesPersonID is not null

SalesOrderID SalesPersonID OrderDate               OrderRank

------------ ------------- ----------------------- ---

43659        279           2001-07-01 00:00:00.000 1

43660        279           2001-07-01 00:00:00.000 2

43681        279           2001-07-01 00:00:00.000 3

43684        279           2001-07-01 00:00:00.000 4

43685        279           2001-07-01 00:00:00.000 5

43694        279           2001-07-01 00:00:00.000 6

43695        279           2001-07-01 00:00:00.000 7

43696        279           2001-07-01 00:00:00.000 8

43845        279           2001-08-01 00:00:00.000 9

43861        279           2001-08-01 00:00:00.000 10

. . . More

48079        287           2002-11-01 00:00:00.000 1

48064        287           2002-11-01 00:00:00.000 2

48057        287           2002-11-01 00:00:00.000 3

47998        287           2002-11-01 00:00:00.000 4

48001        287           2002-11-01 00:00:00.000 5

48014        287           2002-11-01 00:00:00.000 6

47982        287           2002-11-01 00:00:00.000 7

47992        287           2002-11-01 00:00:00.000 8

48390        287           2002-12-01 00:00:00.000 9

48308        287           2002-12-01 00:00:00.000 10

. . . More

PARTITION BY supports other SQL Server aggregate functions including MIN and MAX.