SQL Analytics - Ranking with ordinal suffix

SQL Analytics provides a fairly simple mechanism for determining positional rank within a set of results.

Before I demonstrate that query - which is already found in many good libraries - I thought I'd show how we could take it a step further and add the ordinal suffix (st, nd, rd, th) to a result.

We can do this using date format masks

with placing as (select rownum rn from dual connect by level < 5)
select to_char(to_date('2013-01-'||rn,'yyyy-mm-dd')
              ,'fmddth') ordinal_suffix
from placing
/

ORDINAL_SUFFIX
--------------
1st            
2nd            
3rd            
4th

After adding the year/month to our position, we convert the result to a date - then convert it back to our desired output using TO_CHAR. The "fm" removes the leading zero, and we can obviously ignore the year/month from the output. On a side note, something I discovered while writing this query is the inability to concatenate values in the ANSI date expression.

select to_char(date '2013-01-'||1,'fmddth') from dual;

ORA-01847: day of month must be between 1 and last day of month

If you know a way around this, I'd be happy to know.

Now we can combine this expression with the dense_rank() analytical function.

select ename, sal
  ,rank() over (order by sal desc) rank 
  ,dense_rank() over (order by sal desc) dense_rank 
  ,to_char(to_date('2013-01-'||dense_rank() over (order by sal desc),'yyyy-mm-dd'),'fmddth')  rankth
from emp

ENAME             SAL       RANK DENSE_RANK RANKTH
---------- ---------- ---------- ---------- ------
KING             5000          1          1 1st    
FORD             3000          2          2 2nd    
SCOTT            3000          2          2 2nd    
JONES            2975          4          3 3rd    
BLAKE            2850          5          4 4th    
CLARK            2450          6          5 5th    
ALLEN            1600          7          6 6th    
TURNER           1500          8          7 7th    
MILLER           1300          9          8 8th    
WARD             1250         10          9 9th    
MARTIN           1250         10          9 9th    
ADAMS            1100         12         10 10th   
JAMES             950         13         11 11th   
SMITH             800         14         12 12th   

 14 rows selected 

Cool, huh?

Analytical functions essentially calculate another column of values based on the data queried. I've included 3 examples

1. "RANK" - demonstrates most of it is semantics, in this case you only need to provide which column you would like the ranking to order with.
2. "DENSE_RANK" - shows slightly different rules in the numbers generated in the rank. ie - do we get a bronze?
3. "RANKTH" - combines the ranking with date formatting to make it look cool

Probably nifty for these soccer world cup APEX apps I hear people are creating... just don't try go above about 30 places ;-)