PERCENT_RANK() returns the position of a row within the result set. In contrast to RANK() function, PERCENT_RANK() ranks rows between 0 and 1, both inclusive.

Computation formula used by PERCENT_RANK():

     (RANK() – 1) / (Number of Rows – 1)

     where, RANK() is the rank of the row within the result set.

Using PERCENT_RANK():

PERCENT_RANK() takes and ORDER BY clause as argument:

SELECT [Year], [Month], [Amount],
       PERCENT_RANK() OVER (ORDER BY [Amount]) [PERCENT_RANK],
       RANK() OVER (ORDER BY [Amount]) [RANK]
FROM   dbo.SalesData

Result Set:

Year        Month       Amount                PERCENT_RANK           RANK
2011        1           1000.00               0                      1
2011        2           2000.00               0.142857142857143      2
2010        4           2000.00               0.142857142857143      2
2011        3           3000.00               0.428571428571429      4
2011        4           4000.00               0.571428571428571      5
2010        1           5000.00               0.714285714285714      6
2010        2           6000.00               0.857142857142857      7
2010        3           7000.00               1                      8

(8 row(s) affected)

PERCENT_RANK for row 4 (Year = 2011, Month = 3) is calculated as:

(4 – 1.00) / (8 – 1.00) = 0.4285714..

PARTITION BY:

You can use PARTITION BY clause with PERCENT_RANK() to divide result set into partitions:

SELECT [Year], [Month], [Amount],

       PERCENT_RANK() OVER (PARTITION BY [Year] ORDER BY [Amount]) [PERCENT_RANK],

       RANK() OVER (PARTITION BY [Year] ORDER BY [Amount]) [RANK]

FROM   dbo.SalesData

Result Set:

Year        Month       Amount                PERCENT_RANK           RANK
2010        4           2000.00               0                      1
2010        1           5000.00               0.333333333333333      2
2010        2           6000.00               0.666666666666667      3
2010        3           7000.00               1                      4
2011        1           1000.00               0                      1
2011        2           2000.00               0.333333333333333      2
2011        3           3000.00               0.666666666666667      3
2011        4           4000.00               1                      4

(8 row(s) affected)

Hope This Helps! Cheers!

Reference : Vishal (http://SqlAndMe.com)

Filed under: SQLServer, SQLServer "Denali"