IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Retrieve the Second highest salary of each dept


IBM Mainframe Forums -> Mainframe Interview Questions
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
afroz alam

New User


Joined: 02 Jul 2007
Posts: 13
Location: India

PostPosted: Thu Jul 05, 2007 5:03 pm
Reply with quote

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
View user's profile Send private message
Nimesh.Srivastava

New User


Joined: 30 Nov 2006
Posts: 78
Location: SINGAPORE

PostPosted: Thu Jul 05, 2007 7:01 pm
Reply with quote

afroz alam,
I think an commonly asked interview question (you are asked to do it in one query) icon_lol.gif
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
View user's profile Send private message
Nimesh.Srivastava

New User


Joined: 30 Nov 2006
Posts: 78
Location: SINGAPORE

PostPosted: Thu Jul 05, 2007 7:18 pm
Reply with quote

oops didn't saw the dept part icon_redface.gif
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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> Mainframe Interview Questions

 


Similar Topics
Topic Forum Replies
No new posts Using Java/C/C++ to retrieve dataset ... Java & MQSeries 6
No new posts Retrieve IMS SubSystem Name IMS DB/DC 2
No new posts retrieve volume records from decollec... DFSORT/ICETOOL 4
No new posts Unable to retrieve Datasets Names usi... CLIST & REXX 20
No new posts Retrieve multiple records with metaco... CA Products 0
Search our Forums:

Back to Top