November 24, 2009

Picking up latest/highest record from a group using rank() function

Consider a example where we have Student, Subject and Mark information stored in different tables.

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.

No comments:

Post a Comment