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
 

 

[Solved]How can we retrieve the 5th maximum salary from an emp....

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

New User


Joined: 28 Nov 2006
Posts: 19
Location: India

PostPosted: Tue Dec 12, 2006 11:58 am    Post subject: How can we retrieve the 5th maximum salary from an emp....
Reply with quote

HI All,

Could you please tell me how can we retrieve the 5th maximum salary of an employees table.
Back to top
View user's profile Send private message

priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Tue Dec 12, 2006 12:14 pm    Post subject: Re: How can we retrieve the 5th maximum salary from an emp..
Reply with quote

Code:
SELECT MAX(SALARY) FROM EMP_TABLE A WHERE 5=(SELECT COUNT(*) FROM EMP_TABLE B WHERE A.SALARY<=B.SALARY);


For Nth Maximum Value:-
Code:
SELECT SALARY FROM EMP_TABLE A WHERE (N-1) IN (SELECT COUNT(*) FROM EMP_TABLE B WHERE A.SALARY < B. SALARY);
Back to top
View user's profile Send private message
nasser_tice

New User


Joined: 28 Nov 2006
Posts: 19
Location: India

PostPosted: Tue Dec 12, 2006 12:36 pm    Post subject: Thanks for your prompt reply but.....
Reply with quote

Hi Priyesh,

I am grateful for your prompt reply. I am a bit confused with how the comparision takes place in the inner query and what number it will retireve....

SELECT COUNT(*) FROM EMP_TABLE B WHERE A.SALARY < B. SALARY);
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Tue Dec 12, 2006 12:56 pm    Post subject: Re: How can we retrieve the 5th maximum salary from an emp..
Reply with quote

Lets Say:-
Code:
EMP      SALARY
10         100
20         200
30         300
40         400
50         500


SELECT COUNT(*) FROM EMP_TABLE B WHERE A.SALARY < B. SALARY

First salary 100 is less than salaries of 4 rows of replica of this table, same way second row 200 is less than salaries of 3 rows of replica of this table. So result of SUB Query would include count as (4,3,2,1,0)

Main query compares the (Nth-1)... let's say we are looking for 3rd highest... So main query looks for (3-1=2) and picks up row for SALARY 300.
Back to top
View user's profile Send private message
nasser_tice

New User


Joined: 28 Nov 2006
Posts: 19
Location: India

PostPosted: Tue Dec 12, 2006 1:03 pm    Post subject:
Reply with quote

Thank you priyesh for your briefing out of my question.
Back to top
View user's profile Send private message
siripuram

New User


Joined: 21 May 2006
Posts: 2
Location: hyderabad

PostPosted: Mon Jan 08, 2007 12:13 pm    Post subject: Re: How can we retrieve the 5th maximum salary from an emp..
Reply with quote

hi.I think we should mention Distinct in inner select .

SELECT E1.SALARY FROM EMP E1 WHERE 5 =(

SELECT COUNT( DISTINCT E2.SALARY ) FROM EMP E2 WHERE
E2.SALARY <= E1.SALARY );
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2275
Location: @my desk

PostPosted: Mon Jan 08, 2007 12:54 pm    Post subject:
Reply with quote

Hi Priyesh

Code:

EMP      SALARY
10         100
20         200
30         300
40         400
50         500


Quote:
So result of SUB Query would include count as (4,3,2,1,0)

Main query compares the (Nth-1)... let's say we are looking for 3rd highest... So main query looks for (3-1=2) and picks up row for SALARY 300.


For the 4th highest salary, 4-1=3....which will pick up SALARY = 400/200?
Or is it (0,1,2,3,4) returned by the SUBQUERY?..Please clarify..

Thanks
Arun
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 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 What is the maximum number of sort wo... Pravina M SYNCSORT 2 Mon Mar 14, 2016 4:51 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


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