Skip navigation.

How does one select the LAST N rows from a table?

From Oracle 9i onwards, the RANK() and DENSE_RANK() functions can be used to determine the LAST N or BOTTOM N rows. Examples:

Get the bottom 10 employees based on their salary


SELECT ename, sal 
  FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal) sal_rank
           FROM emp ) 
 WHERE sal_rank <= 10;

Select the employees getting the lowest 10 salaries


SELECT ename, sal 
  FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal) sal_dense_rank
           FROM emp ) 
 WHERE sal_dense_rank <= 10;

For Oracle 8i and above, one can get the bottom N rows using an inner-query with an ORDER BY clause:

SELECT *
  FROM (SELECT * FROM my_table ORDER BY col_name_1)
 WHERE ROWNUM < 10;

Use this workaround for older (8.0 and prior) releases:

SELECT *
  FROM my_table a
 WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
                FROM my_table b
               WHERE b.maxcol <= a.maxcol)
 ORDER BY maxcol;