Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Retrieve the Second highest salary of each dept

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions
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    Post subject: Retrieve the Second highest salary of each dept
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need to retrieve Julian_date Suganya87 DB2 4 Wed Aug 17, 2016 7:27 pm
No new posts Retrieve current year or month using ... vnktrrd DFSORT/ICETOOL 15 Tue Mar 15, 2016 4:14 pm
No new posts retrieve line commands from the data ... Pedro TSO/ISPF 6 Sat Oct 17, 2015 5:41 am
No new posts DFSORT - Retrieve selective records f... narasimha_devi DFSORT/ICETOOL 8 Thu Aug 06, 2015 4:12 pm
No new posts Retrieve records in the order of alte... jacobdng Compuware & Other Tools 8 Fri Jul 31, 2015 8:16 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us