|
View previous topic :: View next topic
|
| Author |
Message |
anshul_gugnani
New User
Joined: 02 Nov 2009
Posts: 73
Location: Mumbai
|
|
|
|
Hi,
Can Anyone please explain me the logic behind the below query to find the nth max salary in EMP table -
| Quote: |
select sal from emp A where n =
( select count(*) from emp B where A.sal < B.sal )
|
Thanks |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| topic moved where it belongs! |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Suggest you write down 5 "salaries" and manually step thru the process.
This will quickly show what happens when the query processes. |
|
| Back to top |
|
|
anshul_gugnani
New User
Joined: 02 Nov 2009
Posts: 73
Location: Mumbai
|
|
|
|
| Hi |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Well, it is a start. . .  |
|
| Back to top |
|
|
anshul_gugnani
New User
Joined: 02 Nov 2009
Posts: 73
Location: Mumbai
|
|
|
|
Hi, Say I have 5 salaries as -
12, 25, 13, 78, 03
According to
| Quote: |
| select count(*) from emp B where A.sal < B.sal |
each salary will have a count for which it is greater then other salaries in table.
So result for records will be this will be -
3, 1, 3, 0, 4
Now, According to
| Quote: |
select sal from emp A where (n-1) =
|
25 is salary whose count is equal to (n-1).
Please suggest if my understanding is correct. I think this co-related subquery.
Thanks! |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Other than the typo (3, 1, 3, 0, 4) , i believe you have it correctly 
Also - Fromthe db2 manual (i added the bold for emphasis):
| Quote: |
| A subquery is actually executed for each row of R only if it includes a correlated reference. In fact, a subquery with no correlated references is executed just once, whereas a subquery with a correlated reference may have to be executed once for each row. |
|
|
| Back to top |
|
|
anshul_gugnani
New User
Joined: 02 Nov 2009
Posts: 73
Location: Mumbai
|
|
|
|
Hi,
| Quote: |
| Other than the typo (3, 1, 3, 0, 4) , |
Could you please explain as to why this is wrong??
Thanks |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Maybe 3, 1, 2, 0, 4 . . .? |
|
| Back to top |
|
|
|
|
