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.

Testing Hibernate list() DAO method which fetches all records.

Consider the example where you have a static table "VERSION" and the DAO method that fetches all records from VERSION table through Hibernate.

DAO Method:

   public List getAll() {  
     Criteria crit = getSession().createCriteria(Version.class);  
     crit.addOrder(Order.asc("name"));  
     return crit.list();  
   }  

Poor Test case:
Many occasions I have noticed Test cases that directly assert on the number of records being returned.

 public class VersionDaoImplTest extends AbstractTransactionalJUnit4SpringContextTests{  
   @Autowired  
   private VersionDao versionDao;  
   @Test  
   public void shouldReturnTheListOfVersions() {  
     List versions = versionDao.getAll();  
     assertEquals(5, versions.size());  
   }  
 }  

As seen above, this test case tries to assert Only 5 records are there in the Version Table. When a new record is added to this table, this test case would fail.

Proper test case:
Other way of testing this method is given below.

 @Test  
   public void shouldReturnTheListOfVersions() {    
   List versions = ratingsDao.versionDao.getAll();  
     assertNotNull(ratings);  
     int prevSize = ratings.size();  
     String insertSQL = "Insert into VERSION (NAME,VERSION_NO) values (?,?)";  
     this.simpleJdbcTemplate.update(insertSQL, "TST+", 95);  
     this.simpleJdbcTemplate.update(insertSQL, "TST", 96);  
     this.simpleJdbcTemplate.update(insertSQL, "TST-", 97);  
     versions = versionDao.getAll();  
     assertEquals(prevSize + 3, ratings.size());  
   }  

In this test case, we get the number of records currently in the database. We then insert 3 new records into the table and try to assert whether the number of records being returned has increased by 3.

Testing Sort Order:
Also to test the Criteria.addOrder(Order.asc("name")) you can enhance the test case with the following:

 for (int i = 1; i < versions.size(); i++) {  
       assertTrue(versions.get(i - 1).getName().compareTo(versions.get(i).getName()) < 0);  
 }  

This would assert that records are sorted by the NAME.