Percent_Rank Function
Purpose: Within a group of rows, it calculates the relative rank of a row.
Syntax: Percent_Rank () Over ( [partition_by_clause] order_by_clause)
Formula:
Percent_Rank = (R -1)/(N-1)
Where,
R => The row whose rank needs to be found
N => Total number of rows or records.
e.g. We have a set with 10 elements. We need to find the relative rank of the 7th row. Henceforth, applying the values to the above formula, we get
R = 7 , N = 10
So, Percent_Rank = (7-1)/(10-1) = 0.6666666666666667
The range of values return by this function is between 0 to 1 inclusive. The return data type is always a positive number.
Before going to look into some examples of First_Value we will first create the test environment
Run the below script for doing so
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(15),
BelongsTo VARCHAR(15),
MatchPlayed INT,
RunsMade INT,
WicketsTaken INT,
FeePerMatch NUMERIC(16,2)
) ON [PRIMARY]
GO
--Insert records
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Won','India',10,440,10, 1000)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Cricket','India',10,50,17, 4000)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('B. Dhanman','India',10,650,0,3600)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('C. Barsat','India',10,950,0,5000)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Mirza','India',2,3,38, 3600)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('M. Karol','US',15,44,4, 2000)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Hamsa','US',3,580,0, 400)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Loly','US',6,500,12,8000)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Summer','US',87,50,8,1230)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.June','US',12,510,9, 4988)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A.Namaki','Australia',1,4,180, 9999)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Samaki','Australia',2,6,147, 8888)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('MS. Kaki','Australia',40,66,0,1234)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Boon','Australia',170,888,10,890)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('DC. Shane','Australia',28,39,338, 4444)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Noami','Singapore',165,484,45, 5678)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Biswas','Singapore',73,51,50, 2222)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Dolly','Singapore',65,59,1,9999)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Winter','Singapore',7,50,8,128)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.August','Singapore',9,99,98, 890)
-- Project the records
Select * from PlayerTable
/*Result
PlayerID PlayerName BelongsTo MatchPlayed RunsMade WicketsTaken FeePerMatch
1001 A. Won India 10 440 10 1000.00
1002 A. Cricket India 10 50 17 4000.00
1003 B. Dhanman India 10 650 0 3600.00
1004 C. Barsat India 10 950 0 5000.00
1005 A. Mirza India 2 3 38 3600.00
1006 M. Karol US 15 44 4 2000.00
1007 Z. Hamsa US 3 580 0 400.00
1008 K. Loly US 6 500 12 8000.00
1009 S. Summer US 87 50 8 1230.00
1010 J.June US 12 510 9 4988.00
1011 A.Namaki Australia 1 4 180 9999.00
1012 Z. Samaki Australia 2 6 147 8888.00
1013 MS. Kaki Australia 40 66 0 1234.00
1014 S. Boon Australia 170 888 10 890.00
1015 DC. Shane Australia 28 39 338 4444.00
1016 S. Noami Singapore 165 484 45 5678.00
1017 Z. Biswas Singapore 73 51 50 2222.00
1018 K. Dolly Singapore 65 59 1 9999.00
1019 S. Winter Singapore 7 50 8 128.00
1020 J.August Singapore 9 99 98 890.00
*/
Example
Select
PlayerID
,PlayerName
,BelongsTo
,FeePerMatch
,Percent_Rank() Over(Partition By BelongsTo Order By BelongsTo,FeePerMatch) As PercentRank
From PlayerTable
/* Result
PlayerID PlayerName BelongsTo FeePerMatch PercentRank
1014 S. Boon Australia 890.00 0
1013 MS. Kaki Australia 1234.00 0.25
1015 DC. Shane Australia 4444.00 0.5
1012 Z. Samaki Australia 8888.00 0.75
1011 A.Namaki Australia 9999.00 1
1001 A. Won India 1000.00 0
1003 B. Dhanman India 3600.00 0.25
1005 A. Mirza India 3600.00 0.25
1002 A. Cricket India 4000.00 0.75
1004 C. Barsat India 5000.00 1
1019 S. Winter Singapore 128.00 0
1020 J.August Singapore 890.00 0.25
1017 Z. Biswas Singapore 2222.00 0.5
1016 S. Noami Singapore 5678.00 0.75
1018 K. Dolly Singapore 9999.00 1
1007 Z. Hamsa US 400.00 0
1009 S. Summer US 1230.00 0.25
1006 M. Karol US 2000.00 0.5
1010 J.June US 4988.00 0.75
100
Now let us understand how the relative rank for the [Fee Per Match] column has been generated.
Take of case of the row where the Player ID is 1015 i.e. 3rd from the row.
There are altogether 5 rows for the [Belongs to] field “Australia”.
So, we have R = 3 and N = 5.
Placing the values in our formula yields
Percent_Rank = (3 -1)/(5-1) = 2/4 = 0.5
Hope this helps