PERCENT_RANK() function syntax with an example in SQL Server

PERCENT_RANK() function syntax with an example in SQL Server

Introduction: 

In my previous posts we have seen what is Transact-Sql in sql server and What are new top features of T-Sql in server. This is one of the best sql server interview Questions and answers. Watch more ASP.NET, C#.NET, Jquery, Sql Server, HTML5, HTML, CSS, JQUERY UI Interview questions from here. 

Description: 

    PERCENT_RANK () returns the position of a row within the result set.

Example:

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

 Result:

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

·The formula to find PERCENT_RANK() is as follows:

PERCENT_RANK () = (RANK () – 1) / (Total Rows – 1)

                   1st Row => 0 = (1-1) / (8-1)

                   2nd Row => 0.142857142857143 = (2-1) / (8-1)

·        PERCENT_RANK() function value will be

a)   For first element in its group, it will be 0.

b)   For last element in its group, it will be 1.

c)   For remaining elements, it will be

( Rank of Element - 1) / (Total Elements - 1)

If you have any queries or suggestions, please feel free to ask in comments section.