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

Author Message
nasser_tice

New User

Joined: 28 Nov 2006
Posts: 19
Location: India

 Posted: Tue Dec 12, 2006 11:58 am    Post subject: How can we retrieve the 5th maximum salary from an emp.... HI All, Could you please tell me how can we retrieve the 5th maximum salary of an employees table.

priyesh.agrawal

Senior Member

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

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

 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);
nasser_tice

New User

Joined: 28 Nov 2006
Posts: 19
Location: India

 Posted: Tue Dec 12, 2006 12:36 pm    Post subject: Thanks for your prompt reply but..... 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);
priyesh.agrawal

Senior Member

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

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

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

New User

Joined: 28 Nov 2006
Posts: 19
Location: India

 Posted: Tue Dec 12, 2006 1:03 pm    Post subject: Thank you priyesh for your briefing out of my question.
siripuram

New User

Joined: 21 May 2006
Posts: 2

 Posted: Mon Jan 08, 2007 12:13 pm    Post subject: Re: How can we retrieve the 5th maximum salary from an emp.. 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 );
Arun Raj

Moderator

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

Posted: Mon Jan 08, 2007 12:54 pm    Post subject:

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

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics CICS RETRIEVE pshongal CICS 2 Thu Mar 08, 2018 9:15 am Logic to skip Maximum time-stamp and ... vickey_dw DB2 2 Thu Feb 01, 2018 11:15 pm Need to retrieve Julian_date Suganya87 DB2 4 Wed Aug 17, 2016 7:27 pm Retrieve current year or month using ... vnktrrd DFSORT/ICETOOL 15 Tue Mar 15, 2016 4:14 pm What is the maximum number of sort wo... Pravina M SYNCSORT 2 Mon Mar 14, 2016 4:51 pm

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