0

Can anyone explain this query when 2= subquery is referenced. How does SQL think about this

Top 2nd salary:

Select distinct Salary
from Employee e1
where 2=Select count(distinct Salary) from Employee e2 where e1.salary<=e2.salary;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Roga
  • 1
  • 3
  • This query will return a syntax error because the subquery is not surrounded by parentheses. – Gordon Linoff Jan 22 '20 at 22:44
  • Isn't this quite similar to [your previous question](https://stackoverflow.com/questions/59865236/referencing-parent-query-inside-child-query)? – GMB Jan 22 '20 at 22:45
  • Which dbms vendor? Each engine has an EXPLAIN PLAN query which will show you the answer to your question. [How do you interpret a query's explain plan?](https://stackoverflow.com/q/79266/4256677) has good information. – varontron Jan 22 '20 at 22:48

1 Answers1

0

It will find the second highest salary for all employees.

select distinct salary
  from employee e1
 where 2 = (select count(distinct salary)
              from employee e2
             where e1.salary <= e2.salary);

It does it by searching all the employees and compares that salary to other employees salary (in the subquery). But the subquery only finds all the salaries that are identical or higher. Then it uses distinct to find unique ones and the 2 = (..) makes sure there is only 2 different salaries that match. Which would be itself and one higher. That's in the e1.salary <= e2.salary. e1.salary is itself.

So if

  • Peter makes 104
  • Sally makes 104
  • Bob makes 102
  • Emily makes 100

then the subquery for Bob will find the values 102 and 104, because 102 is identical and 104 is higher, and there are no other higher ones. Since there are exactly 2 distinct salaries count(distinct salary), the entire query will return the result from Bob's row. That is 102.

When the row for Emily is evaluated, the subquery will find 100, 102 and 104, because they are all greater or equal to the salary for Emily. But since the count will then be 3, that row is discarded.

Similarly will rows for Peter and Sally be discarded because for those the result of the subquery will be 1.

Scratte
  • 3,056
  • 6
  • 19
  • 26