14 June 2010

Analytic Function bug and National Championship

Yesterday there were the "NL Masters" in my hometown Oosterhout, The Netherlands. This two day track and field event was the National Championship for Athletes in the Master Class.
While volunteering (I was running around all day as a courier) I noticed a familiar name on the list. Toine van Beckhoven, currently ranked first in the PL/SQL Challenge. Small world. Toine finished first on the 400m hurdles in his category and is now the official Dutch National Champion. Congratulations, Toine.
Being involved in this event triggered a question regarding ranking. And we're back to analytic functions... ;)

In the Oracle Documentation it says that the DENSE_RANK is the Olympic Ranking:

... or the maximum (LAST) dense rank (also called olympic rank).

The major difference between the RANK and the DENSE_RANK function is the way the hand out numbers after a draw is encountered.
Taking Scott's EMP table as an example:

SQL> select ename
2 , sal
3 , rank () over (order by sal desc) rk
4 , dense_rank () over (order by sal desc) dr
5 from emp
6 where deptno = 20
7 /

---------- ---------- ---------- ----------
SCOTT 3000 1 1
FORD 3000 1 1
JONES 2975 3 2
ADAMS 1100 4 3
SMITH 800 5 4

Notice how Scott and Ford have the same salary, and notice how the ranking in the last two columns differ. Jones with a salary of 2975 is either ranked third or second - depending on the use of RANK or DENSE_RANK.

According to the officials I spoke to yesterday, if there is a draw and you have a split first place there will be no Silver medal. This would mean that the Olympic Ranking would be RANK and not DENSE_RANK.

Probably I'm mistaken (again) so would appreciate your comments and corrections... :)

Oracle Documentation

No comments:

Post a Comment