Retrieve the Second highest salary of each dept

afroz alam · New User Joined: 02 Jul 2007 Posts: 9 Location: India

Hi all.....

I have emp table . i want to retrieve second highest salary of each department.

What will be the query[/b]

Nimesh.Srivastava · Posted: Thu Jul 05, 2007 7:01 pm Post subject:

afroz alam,
I think an commonly asked interview question (you are asked to do it in one query)

anyways here's the query which would do so

Nimesh.Srivastava · Posted: Thu Jul 05, 2007 7:18 pm Post subject:

oops didn't saw the dept part

I am not sure it can be done in a single query or not but if you can use a cursor then, it would be like this
cursor to select distinct dept- for each dept
select dept,emp_sal
from emp
where emp_sal e1 < (select max(emp_sal) from emp e2 where
e2.dept = :h_dept)
and e1.dept = :h_dept
order by emp_sal desc
fetch first 1 rows only;
print dept,salary
cursor close

Thanks
Nimesh