Re: Find employee with third salary!

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.

There are a number of problems with your answer as presented here.

1. DISTINCT is very costly and accomplishes nothing.
2. ROWNUM=3 will never be returned by your query because the query never returns ROWNUM 1 and 2
3. rownum = 1 -- does nothing
4. empid int -- missing comma
5. pctfree -- not specified
6. pctused -- not specified
7. tablespace -- not specified
8. varchar(50) -- absolutely unacceptable ... must be VARCHAR2

I know I'm being picky but in an interview these are the things that make the difference. Someone that made the VARCHAR vs VARCHAR2 mistake would likely never get a second look unless they were looking for a job writing Pro*C.

The answer I would have wanted to see is:

SELECT *
FROM (
   SELECT empid,ename,salary,
   RANK() OVER (ORDER BY salary DESC) EMPRANK    FROM employee)
WHERE emprank = 3;

Understanding that, with different data, there might not be one.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)