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
 

 

how to calculate the 2nd max salary in a emp table.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
iknow

Active User


Joined: 22 Aug 2005
Posts: 413
Location: Colarado, US

PostPosted: Thu Oct 13, 2005 10:05 am    Post subject: how to calculate the 2nd max salary in a emp table.
Reply with quote

Hi All

I have a employee table with a salary field. How to calculate the second maximum salary in the employee table. Please provide the query.
Back to top
View user's profile Send private message

sridevi2005

New User


Joined: 15 Sep 2005
Posts: 42

PostPosted: Thu Oct 13, 2005 11:25 am    Post subject: Re: how to calculate the 2nd max salary in a emp table.
Reply with quote

Hi,

Try the following query.

Select min(salary) from employee
where salary > select salary from employee
where salary > avg(sal).

Please let me know if u have any query.

Thanks,
Sridevi
Back to top
View user's profile Send private message
mathiprakash

New User


Joined: 29 Apr 2005
Posts: 14
Location: Pune, India.

PostPosted: Thu Oct 13, 2005 12:26 pm    Post subject:
Reply with quote

Hi,

This topic has already been disscussed so many times.

Not only the second maximum, you can find for any Nth max of a column.

For more information,
http://www.ibmmainframes.com/viewtopic.php?t=1472&highlight=maximum

Regards,
Matty.
Back to top
View user's profile Send private message
radhakrishnan82

Active User


Joined: 31 Mar 2005
Posts: 436
Location: chennai, India

PostPosted: Thu Oct 13, 2005 12:30 pm    Post subject:
Reply with quote

try this code:

Code:
SELECT DISTINCT (SALARY)
FROM EMPTABLE A
WHERE 2 =
(SELECT COUNT (DISTINCT (B.SALARY))
FROM EMPTABLE B
WHERE A.SALARY <= B.SALARY);


Try the following to find the 'n'th max salary of the table:

Quote:
Code:
SELECT EMPSAL FROM EMPTABLE X
WHERE (n-1) IN
(SELECT COUNT(*) FROM EMPTABLE Y
WHERE X.EMPSAL > Y.EMPSAL)


Hope this helps.
Back to top
View user's profile Send private message
Kotes

New User


Joined: 03 May 2005
Posts: 5

PostPosted: Thu Oct 13, 2005 1:42 pm    Post subject: 2nd Maximum salary
Reply with quote

Hi Iknow,
What radhakrishnan wrote is sucessfully works.
Back to top
View user's profile Send private message
jeyakanthan

New User


Joined: 18 May 2005
Posts: 12
Location: chennai

PostPosted: Fri Oct 14, 2005 7:48 pm    Post subject: Re: how to calculate the 2nd max salary in a emp table.
Reply with quote

this is simple query
select max(salary) from emp-table where salary NOT IN
(select max(salary) from emp-table)
Back to top
View user's profile Send private message
nijugopalan

New User


Joined: 15 Oct 2005
Posts: 15
Location: pune

PostPosted: Thu Oct 20, 2005 8:03 pm    Post subject: Re: how to calculate the 2nd max salary in a emp table.
Reply with quote

select max sal from emp where sal<(select max sal from emp)


is this correct
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Wanted to calculate TRK when i have B... Balu5491 JCL & VSAM 7 Wed Jul 19, 2017 5:43 pm
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 18 Thu Jul 13, 2017 2:11 pm
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm


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