View previous topic :: View next topic
|
Author |
Message |
afroz alam
New User
Joined: 02 Jul 2007 Posts: 13 Location: India
|
|
|
|
Hi all.....
I have emp table . i want to retrieve second highest salary of each department.
What will be the query[/b] |
|
Back to top |
|
|
Nimesh.Srivastava
New User
Joined: 30 Nov 2006 Posts: 78 Location: SINGAPORE
|
|
|
|
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
Code: |
select empid,emp_sal
from emp
where emp_sal < (select max(emp_sal) from emp)
order by emp_sal desc
fetch first 1 rows only; |
Thanks
Nimesh |
|
Back to top |
|
|
Nimesh.Srivastava
New User
Joined: 30 Nov 2006 Posts: 78 Location: SINGAPORE
|
|
|
|
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 |
|
Back to top |
|
|
|