I want a query such that we can find the nth rank student in a class with respect to their marks. Really appreciated for the reply. Thank you.
asked
Query to get the nth rank student in a class?
I'm pretty sure this was already asked and answered, may want to do a quick search in the forum for 'RANK'
Yes, rank was discussed but this may be a different twist. And if you ask for student #10 but two are tied for 9th... what does that mean? Should we return #11 or both of #9? I'm going to look into this when I have a few minutes to spend on becoming hopelessly confused. But one constructive thing I will do right now is retag a couple of queries with the keyword "RANK."
Here are two types of answers for your question 1) select mark from student s where n=(select count(*) from student s1 where s1.mark>s.mark);
- This will return the n th rank student. If there are more than one then it'll return all of them.
2) select min(marks) as marks from (select top n * from student order by marks desc) as S1
- This will return the extact n th rank student. If we want more clarity of generating this we need to give complex condition in the subquery on marks field.
