CODEWITHSUNDEEP
sql-server
RAHUL
RAHUL

Reputation: 167

Need help understanding an SQL query

I have a table in SQL, Employee_Details, having three columns, Name, Age and Salary. I came across this query to select the highest salary from the table which goes like this.

SELECT * 
FROM Employee_Details e 
WHERE 0 = (
   SELECT count(DISTINCT Salary) 
   FROM Employee_Details 
   WHERE Salary > e.Salary
)

I have no idea about what the '0' signifies. Could anyone please give me an idea.

Upvotes: 1

Views: 67

Answers (3)

user418938
user418938

Reputation:

Your query could be written as SELECT TOP 1 WITH TIES * FROM Employee_Details e ORDER BY Salary DESC (in T-SQL dialect). Condition in original query checks if the number of distinct values of Salary greater than Salary value in e is zero (i.e. there are no rows with greater Salary).

Upvotes: 0

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

Get all from table Employee_details where count of salary is 0

Upvotes: 0

marc_s
marc_s

Reputation: 754518

It's just a condition that says that the count from the Employee_Details table in the inner SELECT query must be 0 (no one can have a higher salary than the Employee selected in the outer SELECT)

The approach to do it this way seems a bit odd to me..... I would probably have used something like this:

SELECT (columns) 
FROM dbo.Employee_Details e 
WHERE e.Salary = (SELECT MAX(Salary) FROM dbo.Employee_Details)

which should produce the same result - only it seems a lot clearer to me what you're trying to achieve.

Upvotes: 1

Related Questions