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.
Table Structure:
- STUDENT (ID, Name, Class)
- SUBJECT (ID, Name, Year)
- MARK (Subject_ID, Student_ID, Score)
SQL
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
In the above example, instead of joining MARK table directly, we use Oracle rank() function to get the rank column also.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.