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

Question

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) =

If result of second select will be equal 1 then you get all records from Employee Emp1 else not equal 1 then you do not get any data — Roman Marusyk, Jul 21 '15 at 12:48
It's just backwards - it's the same as saying `Where (Select Count(blah) ...) = 1)` Whoever wrote it probably does `Right Join`s too. — Siyual, Jul 21 '15 at 12:48
http://stackoverflow.com/questions/3720735/what-does-where-1-mean-in-sql — Doolius, Jul 21 '15 at 12:48

score 0 · Answer 1 · answered Jul 21 '15 at 12:48

0

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.

answered Jul 21 '15 at 12:48

D Stanley

149,601
11
178
240

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

1 Answers1