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.