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

Author Message
rahulbose

New User

Joined: 21 Feb 2005
Posts: 4

 Posted: Mon Feb 28, 2005 6:53 pm    Post subject: Select Second Maximum from a TABLE How can i find second max salary from a table

mcmillan

Joined: 18 May 2003
Posts: 1200
Location: India

Posted: Mon Feb 28, 2005 9:59 pm    Post subject:

Dear Rahul,

I think this is answered before. Anyway:

The query to find the SECOND MAXIMUM salary:

 Quote: SELECT MAX(SALARY) FROM EMPTABLE WHERE SALARY < (SELECT MAX(SALARY) FROM EMPTABLE) ;

To find the SECOND MINIMUM salary:

 Quote: SELECT MIN(SALARY) FROM EMPTABLE WHERE SALARY > (SELECT MIN(SALARY) FROM EMPTABLE) ;

To find the Nth MAXIMUM SALARY of the table:

 Quote: SELECT SALARY FROM EMPTABLE A WHERE (N-1) IN (SELECT COUNT(*) FROM EMPTABLE B WHERE A.SALARY > B. SALARY)
ashok_bln
Warnings : 1

New User

Joined: 19 Feb 2005
Posts: 5
Location: bangalore

 Posted: Mon Feb 28, 2005 10:23 pm    Post subject: i think this will help u select max(sal) from table1 where sal<(select max(sal) from table1) table1 is table name sal is salary cloumn name regards ashok
mcmillan

Joined: 18 May 2003
Posts: 1200
Location: India

 Posted: Mon Feb 28, 2005 10:41 pm    Post subject: Re Dear Ashok, Is there any difference from the query posted above?

Active User

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

 Posted: Fri Apr 08, 2005 4:28 pm    Post subject: You can also find the distinct second maximum salary from the employee table: SELECT DISTINCT (SALARY) FROM EMPTABLE A WHERE 2 = (SELECT COUNT (DISTINCT (B.SALARY)) FROM EMPTABLE B WHERE A.SALARY <= B.SALARY);
chandramouli.v

New User

Joined: 21 Mar 2005
Posts: 12

 Posted: Fri Apr 08, 2005 4:33 pm    Post subject: Re: Select Second Maximum from a TABLE SELECT * FROM a WHERE N = (SELECT count(DISTINCT(b.col1)) FROM b WHERE a.col1<=b.col1) N is the nth highest value mention the value
karuna

New User

Joined: 03 May 2005
Posts: 10
Location: bangalore

Posted: Wed May 18, 2005 5:07 pm    Post subject:

Hi Mcmillan,

m not getting the query of nth maximum salary which you have given below. Could you please explain the query?

mcmillan wrote:
Dear Rahul,

I think this is answered before. Anyway:

The query to find the SECOND MAXIMUM salary:

 Quote: SELECT MAX(SALARY) FROM EMPTABLE WHERE SALARY < (SELECT MAX(SALARY) FROM EMPTABLE) ;

To find the SECOND MINIMUM salary:

 Quote: SELECT MIN(SALARY) FROM EMPTABLE WHERE SALARY > (SELECT MIN(SALARY) FROM EMPTABLE) ;

To find the Nth MAXIMUM SALARY of the table:

 Quote: SELECT SALARY FROM EMPTABLE A WHERE (N-1) IN (SELECT COUNT(*) FROM EMPTABLE B WHERE A.SALARY > B. SALARY)

Active User

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

 Posted: Wed May 18, 2005 5:28 pm    Post subject: Re: Select Second Maximum from a TABLE What does the error message says while querying?
ajay_dheepak

New User

Joined: 12 May 2005
Posts: 32
Location: Chennai

 Posted: Thu May 19, 2005 3:03 pm    Post subject: Quote: SELECT SALARY FROM EMPTABLE A WHERE (N-1) IN (SELECT COUNT(*) FROM EMPTABLE B WHERE A.SALARY > B. SALARY) It didnt give any error message. Just explain the query
karuna

New User

Joined: 03 May 2005
Posts: 10
Location: bangalore

Posted: Sat May 21, 2005 11:24 am    Post subject: Re: Select Second Maximum from a TABLE

 radhakrishnan82 wrote: What does the error message says while querying?

Hi,

Not getting any error message but m not getting the query.So please explain it.
paru

New User

Joined: 24 Jun 2005
Posts: 9
Location: INDIA

 Posted: Fri Aug 19, 2005 3:52 pm    Post subject: Hi karuna, Let me explain the query with an example.Suppose you want find the third highest salary.In this case N=3. The sub Query SELECT COUNT(*) FROM EMPTABLE B WHERE A.SALARY > B. SALARY will be 2 only when there are two values greater than the answer you want.The condition, SELECT SALARY FROM EMPTABLE A WHERE 2 IN 2, will then become satisfied and you will be getting the third maximum salary. Hope this makes sense. Cheers, Paru
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics How to select record x+7 or x+2 based... JoAnn Kulcyk SYNCSORT 6 Tue Jan 16, 2018 10:49 pm 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 Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm

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