|
View previous topic :: View next topic
|
| Author |
Message |
nasser_tice
New User
Joined: 28 Nov 2006
Posts: 19
Location: India
|
|
|
|
HI All,
Could you please tell me how can we retrieve the 5th maximum salary of an employees table. |
|
| Back to top |
|
 |
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
| 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 |
|
|
nasser_tice
New User
Joined: 28 Nov 2006
Posts: 19
Location: India
|
|
|
|
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 |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
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 |
|
|
nasser_tice
New User
Joined: 28 Nov 2006
Posts: 19
Location: India
|
|
|
|
| Thank you priyesh for your briefing out of my question. |
|
| Back to top |
|
|
siripuram
New User
Joined: 21 May 2006
Posts: 2
Location: hyderabad
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
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 |
|
|
|
|
