Loading

NTILE in SQL Server

NTILE in SQL Server

Sukhada Vader

8y863Learn iOS Programming

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manav Pandya8y ago
Accepted 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
+1
Sukhada Vader8y ago
@ Piyush Thanku so mch..
0
Piyush Pansuriya8y ago
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.
+1
Piyush Pansuriya8y ago
Hello,

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