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.

2 Responses to More TSQL Analytic Functions – PERCENT_RANK

  1. Pingback: » My Top 10 TSQL Enhancements in SQL Server 2012 Steve Stedman

  2. Pingback: » Code Camp in Redmond Today— Steve Stedman

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>