question

OraLearner avatar image
OraLearner asked

Query to get the nth rank student in a class?

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.

queryoraclerank
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'm pretty sure this was already asked and answered, may want to do a quick search in the forum for 'RANK'
0 Likes 0 ·
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."
0 Likes 0 ·

1 Answer

· Write an Answer
OracleApprender avatar image
OracleApprender answered

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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you. Its working fine.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.