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

Submitted by admin on Sat, 2005-10-15 07:22
Body: 

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;

»

Comments

Submitted by rajeevsingh_mca (not verified) on Sat, 2005-11-19 00:28. 

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

»

Submitted by umesh (not verified) on Fri, 2005-12-30 05:42. 

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

»

Submitted by manorama (not verified) on Mon, 2006-01-09 09:36.

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;

»

Submitted by mohan_krishnan83 on Mon, 2006-07-31 22:45.

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

»

Submitted by milleniumaire on Fri, 2010-04-30 04:32.

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

»