Skip navigation.

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

From Oracle 9i onwards, the RANK() and DENSE_RANK() functions can be used to determine the TOP N rows. Examples:

Get the top 10 employees based on their salary


SELECT ename, sal 
  FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) sal_rank
           FROM emp ) 
 WHERE sal_rank <= 10;

Select the employees making the top 10 salaries


SELECT ename, sal 
  FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal DESC) sal_dense_rank
           FROM emp ) 
 WHERE sal_dense_rank <= 10;

For Oracle 8i and above, one can get the Top N rows using an inner-query with an ORDER BY clause:

SELECT *
  FROM (SELECT * FROM my_table ORDER BY col_name_1 DESC)
 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 DESC;

SQL> select rownum,ename,sal

SQL> select rownum,ename,sal from (select ename,sal from emp  order by sal desc) where rownum <=4;

    ROWNUM ENAME             SAL
---------- ---------- ----------
         1 KING             5000
         2 SCOTT            3000
         3 FORD             3000
         4 JONES            2975

select

select rownum, ename, empno, sal 
from (select ename,empno,sal
      from emp order by sal desc) 
where rownum < 5

To get top 10 earners of the

To get top 10 earners of the company:

select * from(select * from emp order by sal desc) where rownum<11; OR

select * from(select * from emp order by sal desc) where rownum<=10;

Top 10 earners of the company

Hi
Check the follwing SQL Query

Select * from (
select rownum u,a.* from (select emplcode,nvl(basicpay,0) from emplmast where
nvl(basicpay,0) > 0 order by basicpay desc ) a ) where u <=5

Will return a TOP 5 salaried Employees.

Regards
Mohana Krishnan R
ACCL

Nice one!

Great article. Concise and understandable. Just what I was looking for.