More TSQL Analytic Functions – PERCENT_RANK

Percent rank is defined as the number of values that are the same or less than the current value divided by one less than the number of values. Percent rank is different than PERCENTILE, stay tuned for PERCENTILE_DISC and PERCENTILE_CONT which are different from PERCENT_RANK.

For this example I will be using the same revenue table in the sample database that I set up for the LEAD and LAG posting yesterday.

select DepartmentID, Revenue, Year,
       RANK() OVER(ORDER BY Revenue) as RankYear,
       PERCENT_RANK() OVER(ORDER BY Revenue) as PercentRank
from REVENUE
where DepartmentID = 1;

RANK() will give us the position in the overall list. For instance in a list with 15 items, the two values tied for the bottom value will be position 1. The largest number will be position 15, and the two that are tied in the middle will be at position 8.

PERCENT_RANK() shows the percentage of values that are less than or equal to the current value.

So in the above example you can see that the year 2002 was the best revenue year with $90,000 in revenue, and 2004 with 2012 were the worst years for revenue with $10,000 in revenue.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!