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
ENAME SAL RK DR
---------- ---------- ---------- ----------
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... :)