Write a query to obtain the %Rank of students using SQL Server 2005's NTile function

 Posted by Niladri.Biswas on 7/19/2012 | Category: Sql Server Interview questions | Views: 2022 | Points: 40
Answer:

Declare @t Table(StudentName Varchar(50),Marks int) 

Insert Into @t Values
('Name1',98),('Name2',78),('Name3',77),('Name4',67),('Name5',99),('Name6',99),
('Name7',93),('Name8',91),('Name9',69),('Name10',89)

Select
StudentName
,Marks
,PercentileRank = NTILE(100) OVER (Order By Marks)
From @t

/* Result */

StudentName Marks PercentileRank
Name4 67 1
Name9 69 2
Name3 77 3
Name2 78 4
Name10 89 5
Name8 91 6
Name7 93 7
Name1 98 8
Name5 99 9
Name6 99 10


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response