12-10-2013 08:02 AM
I am so puzzled that how does this code give the 3rd highest value from the dataset anand.aus. Please explain the code
"where 3= (select count(distinct (Salary)) from anand.aus b where a.salary<=b.salary)"
to me so that it could be eaiser to understand as I am a newbie.
from anand.aus a
where 3= (select count(distinct (Salary)) from anand.aus b where a.salary<=b.salary);
12-10-2013 08:48 AM
It is an extremely ineficcient way to get the third highest value.
The logic is as follow:
For each value of Salary in the outer query select a.Salary, a subquery is executed on the same exact single variable of the same dataset with the following embeded logic:
So for each value of a.salary, a full subquery is executed to count the number of salaries that are above or equal that value (b.salary => a.salary). If the count is equal to 3, hence if if the current a.salary has only exactly 3 values that are greater or equal to it, then this record is kept and output from the outer query as per the where 3= portion of the logic.
In particular, if the third highest salary in your dataset had replicates e.g. if your data were something like 50000 49000 48000 48000 48000 47000 46000 45000 44000
then the 3rd highest salary would actualy be output 3 times because all the records with salary=48000 would have the subquery output 3 as the value from the select count(distinct salary). ..