Alternate of sql server TOP in oracle

Question

How to get top 3 records in oracle pl sql?i am new to oracle,earlier i have used sql server.

My requirement is to get distinct top 3 records of Column X.

possible duplicate of [Oracle SELECT TOP 10 records](http://stackoverflow.com/questions/2498035/oracle-select-top-10-records) — Ben, Dec 02 '12 at 09:14

Rahul Tripathi · Accepted Answer · 2012-12-02T09:18:59.017

1

Try this to retrieve the Top N records from a query, you can use the following syntax::-

 SELECT *
 FROM (your ordered query) alias_name
 WHERE rownum <= Rows_to_return

Example:-

SELECT *
 FROM (select * from suppliers ORDER BY supplier_name) suppliers2
 WHERE rownum <= 3

edited Dec 02 '12 at 09:18

answered Dec 02 '12 at 09:12

Rahul Tripathi

1

There is no need for `order by rownum`, it is a psuedocolumn generated in the order that rows are returned, i.e. by definition it's orderd. – Ben Dec 02 '12 at 09:13

score 0 · Answer 2 · answered Dec 02 '12 at 09:08

0

This may help you

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

answered Dec 02 '12 at 09:08

Anoop Vaidya

This will incur an additional table or index scan for no reason. – Ben Dec 02 '12 at 09:14

2 Answers2