PartitioningRanking within Groups
We have seen ROW_NUMBER and RANK operating over an ORDER clause with one or more columns. The ORDER BY must be specified for RANK() and DENSE_RANK(), but is optional for ROW_NUMBER(). That is, selecting
will number the rows in an arbitrary fashion.
The other clause that may be used with RANK, DENSE_RANK, and ROW_NUMBER is a partitioning clause. This allows you to perform the function over subsets of data.
The query in Listing 7 will obtain the ranking of clients for each month in the past year.Listing 7. Ranking in partitions
CLIENT, MONTHEND, SALEVOL,
RANK() OVER (PARTITION BY MONTHEND
ORDER BY SALEVOL DESC) AS MONTHRANK
WHERE MONTHEND > CURRENT DATE - 1 YEAR
ORDER BY MONTHEND
As with the ORDER clause, it is possible to specify multiple PARTITION columns, separated by commas.