If we need to retrieve the toppers in every subject for a given year through SQL, the usual way is to use a Subquery, sort the result descending and pick up the ROWNUM=1 record.
Instead of doing all that, Oracle's rank() function can help us to achieve the same in a simple way.
- STUDENT (ID, Name, Class)
- SUBJECT (ID, Name, Year)
- MARK (Subject_ID, Student_ID, Score)
In the above example, instead of joining MARK table directly, we use Oracle rank() function to get the rank column also.
SELECT STUDENT.*, MARK.Mark, SUBJECT.Name FROM STUDENT, SUBJECT, (SELECT *, rank() OVER( partition by Subject_ID order by Score desc) rank FROM MARK )MARK WHERE MARK.Student_ID=STUDENT.ID AND MARK.Subject_ID=SUBJECT.ID AND SUBJECT.Year=10 AND rank=1
Here we partition the table by Subject_Id, apply the descending order by Score and then rank the result. When we add the WHERE clause on rank=1 it would fetch the toppers in every Subject.
This rank() function is also handy to get the latest record based on date from a given group.