Topic: SQL*Plus and PL/SQL >> How this query works - find nth max salary from a table
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: How this query works - find nth max salary from a table |
| sib |
Posted: Feb 02, 2007 06:08:36 AM |
Total Post: 9
Joined: Nov, 2006
|
Hi,
just now i happen to see a query which finds the nth maximum salary from a given table. can any one explain me how actually this work.
Query is :
SELECT NAME,SALARY FROM EMP_MGR E
WHERE &SAM = (
SELECT COUNT(*) FROM EMP_MGR WHERE E.SALARY <= SALARY
);
where in &sam i will be the position of the rank.
Thanks in advance
sib
|
|
|
 KS |
| Posted: Jun 14, 2007 11:18:00 AM |  |
|
Total Post: 65
Joined: Nov, 2006
|
Hi,
Please find the explanation..
SELECT ENAME,SAL FROM EMP E
WHERE &SAM = (
SELECT COUNT(*) FROM EMP WHERE E.SAL <= SAL
);
The above SQL works on the result of the inner query which is the checked with the position entered by the user.
Take the following records for example,
ENAME SAL
a 1000
b 2000
c 3000
d 4000
e 5000
The value for &SAM= 3 (means the third highest salary)
For each val E.SAL , it checks the count of the inner query by checking how many records has salary
less than or equal to the E.sal.
Thus for the above example,
when the E.SAL checks for the value 3000, the inner query gives a count of 3.
Hence the output
Ename = c Sal= 3000.
Please let me know , if need any more onformation on the above.
Regards,
Sri
|
|
|
|
|
| sib |
| Posted: Jun 14, 2007 11:15:04 PM | |
|
Total Post: 9
Joined: Nov, 2006
|
Thanks srini for your help.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 327 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
