Re: Find employee with third salary!

I dont do enough analytic functions so I always forget how to do them and have to look them up. For an interview an easier solution is as follows: (yeah I know analytic functions are better).

I havent tested this so my syntax may be slightly off...

SELECT *
FROM (
   SELECT empid,ename,salary,rownum row
   FROM employee
   where rownum <=3
   order by salary desc )a
WHERE a.row = 3

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1077519786.522696_at_yasure...
> 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)
>
Received on Mon Feb 23 2004 - 17:10:07 CST