Recipe11.5.Selecting the Top n Records

Recipe 11.5. Selecting the Top n Records

Problem

You want to limit a result set to a specific number of records based on a ranking of some sort. For example, you want to return the names and salaries of the employees with the top five salaries.

Solution

The key to this solution is to make two passes: first rank the rows on whatever value you want to rank on; then limit the result set to the number of rows you are interested in.

DB2, Oracle, and SQL Server

The solution to this problem depends on the use of a window function. Which window function you will use depends on how you want to deal with ties. The following solution uses DENSE_RANK, so that each tie in salary will count as only one against the total:

 1  select ename,sal 2    from ( 3  select ename, sal, 4         dense_rank() over (order by sal desc) dr 5    from emp 6         ) x 7   where dr <= 5 

The total number of rows returned may exceed five, but there will be only five distinct salaries. Use ROW_NUMBER OVER if you wish to return five rows regardless of ties (as no ties are allowed with this function).

MySQL and PostgreSQL

Use a scalar subquery to create a rank for each salary. Then restrict the results of that subquery by rank:

  1  select ename,sal  2    from (  3  select (select count(distinct b.sal)  4            from emp b  5           where a.sal <= b.sal) as rnk,  6         a.sal,  7         a.ename  8    from emp a  9         ) 10   where rnk <= 5 

Discussion

DB2, Oracle, and SQL Server

The window function DENSE_RANK OVER in inline view X does all the work. The following example shows the entire table after applying that function:

  select ename, sal,        dense_rank( ) over (order by sal desc) dr   from emp ENAME      SAL         DR ------- ------ ---------- KING      5000          1 SCOTT     3000          2 FORD      3000          2 JONES     2975          3 BLAKE     2850          4 CLARK     2450          5 ALLEN     1600          6 TURNER    1500          7 MILLER    1300          8 WARD      1250          9 MARTIN    1250          9 ADAMS     1100         10 JAMES      950         11 SMITH      800         12 

Now it's just a matter of returning rows where DR is less than or equal to five.

MySQL and PostgreSQL

The scalar subquery in inline view X ranks the salaries as follows:

  select (select count(distinct b.sal)           from emp b          where a.sal <= b.sal) as rnk,        a.sal,        a.ename   from emp a RNK    SAL ENAME --- ------ -------   1   5000 KING   2   3000 SCOTT   2   3000 FORD   3   2975 JONES   4   2850 BLAKE   5   2450 CLARK   6   1600 ALLEN   7   1500 TURNER   8   1300 MILLER   9   1250 WARD   9   1250 MARTIN  10   1100 ADAMS  11    950 JAMES  12    800 SMITH 

The final step is to return only rows where RNK is less than or equal to five.