Zahid Hossain Zahid Hossain - 6 months ago 9
SQL Question

How does this query work? I am confused in line 1

Employee

===========================
| Employee ID | Salary |
===========================
| 3 | 200 |
| 4 | 800 |
| 7 | 450 |
============================


Query :

SELECT *
FROM Employee Emp1
WHERE (1) = ( --Line1
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)


The above query outputs the 2nd highest salary for the "Employee" table.
How does Line1 work? I am not familiar with the syntax type:

WHERE (1) =

Answer

It might make more sense this way:

SELECT *
FROM Employee Emp1
WHERE 
(SELECT COUNT(DISTINCT(Emp2.Salary))
    FROM Employee Emp2
    WHERE Emp2.Salary > Emp1.Salary)
 = 1

Basically like you said - select all employees with the second highest salary.