Implementation of Ranking Function in Sql Server

Ranking Function :

Ranking functions are new feature in Sql Sever 2005. Ranking functions are functions which allow you to create sequentially numbers.

There are 4 Ranking functions in sql Server :

1 - Row_Number()
2 - Rank()
3 - Dense_rank()
4 - NTILE(n) (Here : n is any number to group the rows in recordset)


Implementation :

[ Just copy & Paste ]

declare @TBL table(EmployeeName Varchar(50),Gender char(1),Age int)

insert into @TBL(EmployeeName,Gender,Age) values('Manish Pathak','M',20)
insert into @TBL(EmployeeName,Gender,Age) values('Milan Kumar','M',15)
insert into @TBL(EmployeeName,Gender,Age) values('Kiran Sharma','F',18)
insert into @TBL(EmployeeName,Gender,Age) values('Vikas Yadav','M',18)
insert into @TBL(EmployeeName,Gender,Age) values('Parul Saxena','F',15)
insert into @TBL(EmployeeName,Gender,Age) values('Rahul Saxena','M',19)

-- 1 - Row_Number()

select SrNo = ROW_NUMBER() over (order by EmployeeName),EmployeeName,Gender,Age from @TBL

select SrNoByGender = ROW_NUMBER() over (partition by Gender order by EmployeeName),EmployeeName,Gender,Age from @TBL

-- 2 - Rank()

select SrNoRankByGender = rank() over (order by Gender),EmployeeName,Gender,Age from @TBL

select SrNoRankByAge = rank() over (partition by Age order by EmployeeName),EmployeeName,Gender,Age from @TBL

-- 3 - Dense_rank()

select SrNoRankByGender = dense_rank() over (order by Gender),EmployeeName,Gender,Age from @TBL

select SrNoRankByAge = dense_rank() over (partition by Age order by EmployeeName),EmployeeName,Gender,Age from @TBL

-- 4 - NTILE(n)

select EmployeeName,Gender,Age,AgeGroup = NTILE(3) over (order by Age) from @TBL

[Note : The difrence between Rank() & Dense_Rank() function is only that the Rank() function provide the gap between number based on RowCount and Dense_Rank() function does not provide the gap between number]


Regards
Manish