Select the LAST N rows from a table in Oracle

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;

Thanks.