Recipe11.9.Ranking Results

Recipe 11.9. Ranking Results

Problem

You want to rank the salaries in table EMP while allowing for ties. You want to return the following result set:

 RNK     SAL --- -------   1     800   2     950   3    1100   4    1250   4    1250   5    1300   6    1500   7    1600   8    2450   9    2850  10    2975  11    3000  11    3000  12    5000 

Solution

Window functions make ranking queries extremely simple. Three window functions are particularly useful for ranking: DENSE_RANK OVER, ROW_NUMBER OVER, and RANK OVER.

DB2, Oracle, and SQL Server

Because you want to allow for ties, use the window function DENSE_RANK OVER:

 1 select dense_rank() over(order by sal) rnk, sal 2   from emp 

MySQL and PostgreSQL

Until window functions are introduced, use a scalar subquery to rank the salaries:

 1 select (select count(distinct b.sal) 2           from emp b 3          where b.sal <= a.sal) as rnk, 4        a.sal 5   from emp a 

Discussion

DB2, Oracle, and SQL Server

The window function DENSE_RANK OVER does all the legwork here. In parentheses following the OVER keyword you place an ORDER BY clause to specify the order in which rows are ranked. The solution uses ORDER BY SAL, so rows from EMP are ranked in ascending order of salary.

MySQL and PostgreSQL

The output from the scalar subquery solution is similar to that of DENSE_RANK because the driving predicate in the scalar subquery is on SAL.