Re: Second highest value when using group by

From: Oradba Linux <techiey2k3_at_comcast.net>
Date: Tue, 07 Dec 2004 03:09:23 GMT
Message-ID: <y99td.621120$mD.495917@attbi_s02>

"Ajit" <ajitsd_at_gmail.com> wrote in message news:1102380656.451212.8770_at_z14g2000cwz.googlegroups.com...
> I want to find the second highest salary earner in each department in
> the example below:
>
> ID Department Salary
> 1 101 400
> 2 101 550
> 3 101 500
> 4 201 400
> 5 201 500
>
> The query I am expecting should return this result set:
>
> ID Department Salary
> 3 101 500
> 4 201 400
> Whats the most efficient way of writing SQL for such a problem?
>

Ajit

This is pretty straightforward if you are using oracle 8.1.6 and above. Not sure if 8.1.5 introduced
analytics . Anyways below is the SQL

create table high ( id number , dept number , salary number);

Table created.

 insert into high values(1,101,400);
 insert into high values(2,101,550);
 insert into high values(3,101,500);
 insert into high values(4,201,400);
 insert into high values(5,201,500);

commit;
select * from
(select id , dept,salary, rank() over(partition by dept order by salary desc) r from high)
where r =2 ;

        ID DEPT SALARY R ---------- ---------- ---------- ----------

         3        101        500          2
         4        201        400          2

Received on Mon Dec 06 2004 - 21:09:23 CST