NTILE in SQL Server

Question

I have Students with there marks and i want to assign rank to students based on their marks. If 2 Students have same marks then assign same rank to both students. like Student Marks Rank A 20 1 B 19 2 C 19 2 D 18 3 E 20 1

Manav Pandya · Answer

Hello I have executed this scenario and i got same output as you exprected : 1 . My Table data : 2 . My Ranking Query : select StudentName , Mark1 , Mark2 , RANK() over( order by Mark1 , Mark2 desc ) As 'Ranking' From dbo.Students 3 . Ouput : As you can see above output screen : Student with name Demo1 and Demo3 got same marks and their respective totals are also = 128 That's why Demo1 and Demo3 got 1st rank If you want to try with single mark column than query : select StudentName , Mark1 , RANK() over( order by Mark1 desc ) As 'Ranking' From dbo.Students Output : I hope this will help you Thanks

Piyush Pansuriya · Answer

Hi, There is a good suggetion from my fried Manav, but there is a diffrence between RANK() and DENSE_RANK() RANK() will skip the numbers, if there is same rank assigned. Like, Student Marks Rank A 20 1 B 19 3 C 19 3 D 18 5 E 20 1 And with DENSE_RANK() will not skip the number.

Piyush Pansuriya · Answer

Hello, Try This SELECT DENSE_RANK() OVER ( ORDER BY MARKS DESC ) AS RANK_NO, STUDENT, MARKS FROM

Sukhada Vader · Answer

@ Piyush Thanku so mch..

NTILE in SQL Server

Answers (4)