Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
iknow

Active User

Joined: 22 Aug 2005
Posts: 413

 Posted: Thu Oct 13, 2005 10:05 am    Post subject: how to calculate the 2nd max salary in a emp table. 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.

sridevi2005

New User

Joined: 15 Sep 2005
Posts: 42

 Posted: Thu Oct 13, 2005 11:25 am    Post subject: Re: how to calculate the 2nd max salary in a emp table. 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
mathiprakash

New User

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

 Posted: Thu Oct 13, 2005 12:26 pm    Post subject: 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.

Active User

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

Posted: Thu Oct 13, 2005 12:30 pm    Post subject:

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.
Kotes

New User

Joined: 03 May 2005
Posts: 5

 Posted: Thu Oct 13, 2005 1:42 pm    Post subject: 2nd Maximum salary Hi Iknow, What radhakrishnan wrote is sucessfully works.
jeyakanthan

New User

Joined: 18 May 2005
Posts: 12
Location: chennai

 Posted: Fri Oct 14, 2005 7:48 pm    Post subject: Re: how to calculate the 2nd max salary in a emp table. this is simple query select max(salary) from emp-table where salary NOT IN (select max(salary) from emp-table)
nijugopalan

New User

Joined: 15 Oct 2005
Posts: 15
Location: pune

 Posted: Thu Oct 20, 2005 8:03 pm    Post subject: Re: how to calculate the 2nd max salary in a emp table. select max sal from emp where sal<(select max sal from emp) is this correct
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics COBOL declaration for table Poha Eater DB2 2 Mon Feb 26, 2018 6:58 pm load and delete table through JCL wit... ram_vizag JCL & VSAM 9 Thu Feb 01, 2018 12:47 am Unloading DB2 table in CSV format alo... grvtomar COBOL Programming 7 Sun Jan 28, 2018 2:05 am Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us