Find employee with third salary!

Hi all,
I was asked this question in an interview which I attended recently and I gave an answer which I wasn't satisfied with. I wish to hear the comments of the community on my answer.

Question: Find the employee with the third largest salary in the organization.

My answer:

CREATE TABLE Employee (

     empid int
     ename varchar(50),
     salary int

insert into Employee values (1, 'emp1', 1000);
insert into Employee values (2, 'emp2', 1001);
insert into Employee values (3, 'emp3', 1002);
insert into Employee values (4, 'emp4', 1003);
insert into Employee values (5, 'emp5', 1000);
insert into Employee values (6, 'emp6', 1001);
insert into Employee values (7, 'emp7', 1003);

SELECT *
FROM Employee
WHERE salary in ( SELECT DISTINCT salary FROM Employee WHERE rownum = 3 ORDER BY Salary DESC)
AND rownum = 1

Please help me with this so that I don't get trapped into another one of these questions again.

Thanks again.
Sridhar. Received on Sun Feb 22 2004 - 23:58:34 CST