viernes, 13 de febrero de 2009

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

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.
E
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)