Re: Find employee with third salary!

On 22 Feb 2004 21:58:34 -0800, sathyam_at_hotmail.com (Sridhar, S.) wrote:

>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.

You don't need DISTINCT because there can be only one row with rownum=3.

how about

select * from Employee where rownum=3 order by salary desc;

K. Received on Mon Feb 23 2004 - 00:15:14 CST