Let's learn Percent Rank function of SQL Server 2012 (Denali)

Niladri.Biswas
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 4087 red flag

In this article, we will look into the Percent Rank function of SQL Server 2012 (Denali).

Introduction

Sql Server 2012 has brought a lot of new functions for the T-Sql developers. In this article we will look into Percent_Rank Function.

Purpose

This function calculates the relative rank of a row within a group of row

Syntax

Percent_Rank () Over ( [partition_by_clause] order_by_clause)

Logic behind Percent_Rank

Well this funciton can be defined as under

Percent_Rank = (R-1)/(N-1)

Where,

R => The row whose rank needs to be found

N => Total number of rows or records.

A case study

Let us first create the environment

IF EXISTS (SELECT * FROM sys.objects WHERE name = N'PlayerTable' AND type = 'U')
    DROP TABLE PlayerTable
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE [dbo].[PlayerTable](
	PlayerID INT IDENTITY(1001,1),
	PlayerName VARCHAR(30),
	BelongsTo VARCHAR(15),	
	Amount NUMERIC(16,2)
) ON [PRIMARY]
GO

--Insert records
INSERT INTO PlayerTable(PlayerName, BelongsTo, Amount) VALUES('Sampark Karmakar','India', 1000)
INSERT INTO PlayerTable(PlayerName, BelongsTo, Amount) VALUES('Hemanth Madan','India', 4000)
INSERT INTO PlayerTable(PlayerName, BelongsTo, Amount) VALUES('P.H. Pradhan','India',3600)
INSERT INTO PlayerTable(PlayerName, BelongsTo, Amount) VALUES('Dabasis Halder','India',5000)
INSERT INTO PlayerTable(PlayerName, BelongsTo, Amount) VALUES('Nuel Kuya','India', 3600)
INSERT INTO PlayerTable(PlayerName, BelongsTo, Amount) VALUES('Manjunath P','Afghanisthan',2000)
INSERT INTO PlayerTable(PlayerName, BelongsTo, Amount) VALUES('Hamim Mirza','Afghanisthan', 400)
INSERT INTO PlayerTable(PlayerName, BelongsTo, Amount) VALUES('Loly Gazia','Afghanisthan',8000)
INSERT INTO PlayerTable(PlayerName, BelongsTo, Amount) VALUES('Suresh Summer','Afghanisthan',1230)
INSERT INTO PlayerTable(PlayerName, BelongsTo, Amount) VALUES('J.June','Afghanisthan', 4988)


-- Project the records
Select * from PlayerTable

Result

Now let us run the below query

Select 
	SerialNumber = ROW_NUMBER() OVER(Order By GETDATE())
	,PlayerID
	,PlayerName
	,BelongsTo
	,Amount
	,Percent_Rank() Over(Partition By BelongsTo Order By BelongsTo,PlayerID,Amount) As PercentRank
From PlayerTable

Result

Now let us understand the logic behind this. Take the example of the 4th Row where the PlayerId = 1009. There are a total of 5 rows for the [Belongs to] field “Afghanisthan”.

So , we have R = 4 (4th row) and N = 5 (Total number of records for "Afghanisthan").

Placing the values in our formula, we get

Percent_Rank = (4-1)/(5-1) = 3/4 = 0.75

How to implement the same in SQL SERVER 2005/2008?

Here is an attempt to simulate Percent Rank function in SQL Server 2005/2008

;With Count_CTE AS
(
	Select BelongsTo ,N = COUNT(BelongsTo)	
	From PlayerTable
	Group By BelongsTo
)
,Rank_CTE AS
(
	Select 
		SerialNumber = ROW_NUMBER() OVER(Order By GETDATE())
		,PlayerID
		,PlayerName
		,BelongsTo
		,Amount
		,R = Rank() Over(Partition By BelongsTo Order by BelongsTo,PlayerID,Amount) 
		-- OR 
		-- R = ROW_NUMBER() Over(Partition By BelongsTo Order by BelongsTo,PlayerID,Amount) 
	From PlayerTable
)
Select r.*,c.N, PercentRank = CAST((r.R -1)  AS DECIMAL(10,2)) / CAST((c.N - 1) AS DECIMAL(10,2))
From Rank_CTE r
Join Count_CTE c On r.BelongsTo = c.BelongsTo

Result

We are using the Rank function here that returns the rank of each row within the partition of a result set.In this case it can also be done using Row_Number function too.

References

Percentile rank

Conclusion

SQL Server 2012 (Denali) seems to be very mature and promising and has embeeded with many new functions.In this article we have looked into the Percentile Rank function, it's internal formula and how we can do the same in lower version.Hope the article will be useful.

Thanks for reading

Page copy protected against web site content infringement by Copyscape

About the Author

Niladri.Biswas
Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
http://www.dotnetfunda.com
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)