CODEWITHSUNDEEP
sqlsql-server
pankaj choudhary
pankaj choudhary

Reputation: 177

What kind of query is this?

In my Employee table, I wanted to find the 3rd highest salary. Someone provided me with the following query to do this:

SELECT * 
FROM   employee C1 
WHERE  3 = (SELECT Count(DISTINCT( C2.salary )) 
            FROM   employee C2 
            WHERE  C2.salary >= C1.salary)

This query works, but I don't how it works. What kind of query is this?

Upvotes: 1

Views: 82

Answers (4)

JamieD77
JamieD77

Reputation: 13949

You can also use Dense_Rank to rank the salaries greatest to least, then select the ones that are ranked 3rd. This will also prevent you from getting the wrong salary if the top 2 are identical like the other answers above mine are doing. This has a better looking execution plan than the Distinct count one also

SELECT * 
FROM (
    SELECT  *,
            DENSE_RANK() OVER (ORDER BY Salary DESC) salary_rank
    FROM    employee e
    ) t
WHERE salary_rank = 3

Upvotes: 1

Daniel Pratt
Daniel Pratt

Reputation: 12077

As others have said, this type of query is called a correlated sub-query. It's a sub-query because there is a query within a query and it's correlated because the inner query references the outer query in its definition.

Consider the inner query:

SELECT Count(DISTINCT( C2.salary )) 
FROM   employee C2 
WHERE  C2.salary >= C1.salary

Conceptually, this inner query will be evaluated once for every row produced by the outer query before the WHERE clause is applied, basically once for every row in employee. It will produce a single value, the count of rows from employee where the salary is less than the salary of the outer row.

The outer query will only return records where the value produced by the inner query is exactly 3. Assuming unique salary values, there is only one row from the employee table where there will be exactly 3 records with a salary value greater than or equal to it (the one row) and that one row is necessarily the third-highest salary value.

It's clever, but unnecessarily weird and probably not as optimal as something more straightforward.

Upvotes: 3

saarrrr
saarrrr

Reputation: 2877

Could also rewrite this with a common table expression.

WITH top_three
AS
(
    SELECT TOP 3 * FROM employee ORDER BY Salary DESC
)
SELECT TOP 1 *
FROM top_three
ORDER BY Salary ASC;

Or, if you need to look for other ranks in this you can use row_number().

WITH ranked
AS
(
    SELECT rank = ROW_NUMBER()OVER(ORDER BY Salary DESC), *
    FROM employee
    ORDER BY Salary DESC
)
SELECT *
FROM ranked
WHERE rank = @whatever_rank_you_want;

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

Maybe a better solution would have been 

SELECT TOP 1 *
FROM (
      SELECT TOP 3 * FROM employee ORDER BY Salary DESC
     ) t
ORDER BY Salary ASC

Easier to read and more efficient than a correlated sub-query.

Upvotes: 1

Related Questions