How do you return the top-N results of a query in Oracle? Why doesn’t the obvious method work?

Hi Vadivel, what do mean by obvious method !

Here are the few examples of how the top-n results are fetched from table...


-- Create table
CREATE TABLE EMPL (ID NUMBER, ENAME VARCHAR2(30), SALARY INTEGER);

-- Inserting values
INSERT INTO EMPL VALUES(1,'Raj', 10500);
INSERT INTO EMPL VALUES(2,'Kumar', 21000);
INSERT INTO EMPL VALUES(3,'Selvam', 30000);
INSERT INTO EMPL VALUES(4,'John', 7500);
INSERT INTO EMPL VALUES(5,'Smith', 35000);
INSERT INTO EMPL VALUES(6,'Vadivel', 40000);
INSERT INTO EMPL VALUES(7,'Xyx', 10000);



Method-1:
----------

SELECT ROWNUM as Rank, ENAME, SALARY FROM
(SELECT ROWNUM, ENAME, SALARY FROM empl ORDER BY SALARY DESC) WHERE ROWNUM <= 3;

RANK ENAME SALARY
---------- ------------------------------ ----------
1 Vadivel 40000
2 Smith 35000
3 Selvam 30000

3 rows selected.


Method-2:
---------

SELECT ENAME,SALARY, RANK() OVER (ORDER BY SALARY DESC) RANK FROM EMPL;


------------------------------ ---------- ----------
ENAME SALARY RANK
------------------------------ ---------- ----------
Vadivel 40000 1
Smith 35000 2
Selvam 30000 3
Kumar 21000 4
Raj 10500 5
Xyx 10000 6
John 7500 7

7 rows selected.

-- If you want to restrict the result set to top 3 rank then use the below query..

SELECT ENAME,SALARY,RANK FROM (SELECT ENAME,SALARY, RANK() OVER (ORDER BY SALARY DESC) RANK FROM EMPL ) WHERE RANK <= 3


ENAME SALARY RANK
------------------------------ ---------- ----------
Vadivel 40000 1
Smith 35000 2
Selvam 30000 3

3 rows selected.


I feel the above given methods are obvious !!!

__________________
Keep smiling...

Hi there,

Here is an example for finding the maximum salary from an employee table...

Use Pubs
Go
Create table Employee
(
EmpID int,
EmpName varchar(10),
EmpSalary money
)
Go
Insert into Employee values (1,'Shilpa',2874)
Insert into Employee values (2,'Mahesh',3712)
Insert into Employee values (3,'Vineela',4222)
Insert into Employee values (4,'Supriya',6500)
Insert into Employee values (5,'Latha',6522)
Go

The Query for the Maximum Salary goes as follows:

Select * From Employee Where Empsalary = (Select max(EmpSalary) From Employee)

__________________
S.VinothkumaR
Behind me is infinite power,
Before me is Endless Possibility,
Around me is Boundless Opportunity,
Why should I fear!