Re: Find employee with third salary!

<nowayjose_at_telus.net> wrote in message
news:ru5j30dckit4emaj73brs8kk7dcad5npqe_at_4ax.com...
> 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;

It won't work because what Oracle will do is

1. Do select * from employee
2. take the first 3 rows of that resultset
3. order those 3 rows by salary.

The explain plan will show this.

I believe that

select * from
(
select empno,salary,rownum rn from employee order by salary desc )
where rn = 3;

will do from the release that allowed order by in inline views (not sure if it existed in 7.3).

analytic functions as others suggested maybe more efficient and both the question and my answer don't deal with ties.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK