LLI Wiki — My Digital Knowledge Base

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;
Mastodon LLIMastodon SocialFosstodon