How Select the TOP N rows from a table?

TOP N rows from a table?

After Oracle 9i there is the RANK() and DENSE_RANK() functions which can be used to determine TOP N rows.

Below is the examples to find the top 5 employees based on their salary.

Using RANK()

SELECT employee_name, salary FROM ( SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) salary_rankFROM employee ) WHERE salary_rank <= 5;

Using Dense_Rank()

SELECT employee_name, salary FROM ( SELECT employee_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) salary_dense_rankFROM employee ) WHERE salary_dense_rank <= 5;

Using inner queryThis is an example of using an inner-query with an ORDER BY clause:

SELECT *FROM (SELECT * FROM employee ORDER BY salary DESC)WHERE ROWNUM < 5;

Using count distinct combination

SELECT *FROM employee eWHERE 5 >= (SELECT COUNT(DISTINCT salary)FROM employee bWHERE b.salary >= e.salary)ORDER BY salary DESC;


EJEMPLO DE SQL

select tabla,registros,fecha from (select tabla,registros,fecha,rank() OVER (order by registros desc) registros_rank
FROM CTM.AUD_REGISTRO_TABLAS
where to_date(fecha,'dd-mm-yyyy')='08-09-2009')
where registros_rank <=10
/
~