Oracle: select the last N rows from a table
Oracle queries to get the bottom N rows using RANK, DENSE_RANK or ROWNUM depending on the version.
Several ways to select the last N rows (or the N lowest values) of an Oracle table, depending on the version.
Oracle 9i and later: RANK and DENSE_RANK
The RANK() and DENSE_RANK() analytic functions return the bottom N rows. Example: the 10 employees with the lowest salaries.
SELECT ename, sal
FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal) sal_rank
FROM emp )
WHERE sal_rank <= 10;
With DENSE_RANK() (no rank gaps on ties):
SELECT ename, sal
FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal) sal_dense_rank
FROM emp )
WHERE sal_dense_rank <= 10;
Oracle 8i and later: inline view with ORDER BY
SELECT *
FROM (SELECT * FROM my_table ORDER BY col_name_1)
WHERE ROWNUM < 10;
Older releases (8.0 and prior)
SELECT *
FROM my_table a
WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
FROM my_table b
WHERE b.maxcol <= a.maxcol)
ORDER BY maxcol;