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.

Posted in SQL 2012, TSQL 2012 Tagged with: , ,

Leave a Reply

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

*


− 2 = one

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=""> <s> <strike> <strong>

Newsletter Signup

Stay informed of Database Corruption Challenge events, and other things happening at SteveStedman.com with my newsletter.
Newsletter signup form.

Stedman Solutions

StedmanSolutionsRemoteSkilledDBA