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
 

 

Select Second Maximum from a TABLE

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

New User


Joined: 21 Feb 2005
Posts: 4

PostPosted: Mon Feb 28, 2005 6:53 pm    Post subject: Select Second Maximum from a TABLE
Reply with quote

How can i find second max salary from a table
Back to top
View user's profile Send private message

mcmillan

Site Admin


Joined: 18 May 2003
Posts: 1202
Location: India

PostPosted: Mon Feb 28, 2005 9:59 pm    Post subject:
Reply with quote

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)
Back to top
View user's profile Send private message
ashok_bln
Warnings : 1

New User


Joined: 19 Feb 2005
Posts: 5
Location: bangalore

PostPosted: Mon Feb 28, 2005 10:23 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
mcmillan

Site Admin


Joined: 18 May 2003
Posts: 1202
Location: India

PostPosted: Mon Feb 28, 2005 10:41 pm    Post subject: Re
Reply with quote

Dear Ashok,

Is there any difference from the query posted above?
Back to top
View user's profile Send private message
radhakrishnan82

Active User


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

PostPosted: Fri Apr 08, 2005 4:28 pm    Post subject:
Reply with quote

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);
Back to top
View user's profile Send private message
chandramouli.v

New User


Joined: 21 Mar 2005
Posts: 12

PostPosted: Fri Apr 08, 2005 4:33 pm    Post subject: Re: Select Second Maximum from a TABLE
Reply with quote

SELECT * FROM <tablename> a
WHERE N = (SELECT count(DISTINCT(b.col1))
FROM <tablename> b WHERE a.col1<=b.col1)

N is the nth highest value
mention the value
Back to top
View user's profile Send private message
karuna

New User


Joined: 03 May 2005
Posts: 10
Location: bangalore

PostPosted: Wed May 18, 2005 5:07 pm    Post subject:
Reply with quote

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)
Back to top
View user's profile Send private message
radhakrishnan82

Active User


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

PostPosted: Wed May 18, 2005 5:28 pm    Post subject: Re: Select Second Maximum from a TABLE
Reply with quote

What does the error message says while querying?
Back to top
View user's profile Send private message
ajay_dheepak

New User


Joined: 12 May 2005
Posts: 32
Location: Chennai

PostPosted: Thu May 19, 2005 3:03 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
karuna

New User


Joined: 03 May 2005
Posts: 10
Location: bangalore

PostPosted: Sat May 21, 2005 11:24 am    Post subject: Re: Select Second Maximum from a TABLE
Reply with quote

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.
Back to top
View user's profile Send private message
paru

New User


Joined: 24 Jun 2005
Posts: 9
Location: INDIA

PostPosted: Fri Aug 19, 2005 3:52 pm    Post subject:
Reply with quote

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 icon_smile.gif
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 SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm


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