CODEWITHSUNDEEP
sql
user16409411
user16409411

Reputation: 1

anyone can help to explain the count(*) in the where clause

When I am doing this problem, write a SQL query to get the nth highest salary from the Employee table.

I saw a solution below:

CREATE FUNCTION getNthHighestSalary(N INT) 
RETURNS INT
BEGIN
    RETURN (
            # Write your MySQL query statement below.
            SELECT e1.Salary
            FROM (SELECT DISTINCT Salary FROM Employee) e1
            WHERE (SELECT COUNT(*) 
                   FROM (SELECT DISTINCT Salary FROM Employee) e2  
                   WHERE e1.Salary < e2.Salary) = N - 1          
           );
END

Question: I do not understand the count(*) in the where clause. I use an example of salary list like (500, 400, 300, 200, 100).

I count the number of e2.Salary > e1.Salary, but the result is (4,3,2,1,0) not (0,1,2,3,4) which is the right number for N-1.

So could anyone explain the logic there?

Thanks

Upvotes: 0

Views: 70

Answers (1)

Tom&#225;š Z&#225;lusk&#253;
Tom&#225;š Z&#225;lusk&#253;

Reputation: 12050

In fact, the count(*) expression is not in where clause, it is in select clause of subquery which is used in where clause, which is syntactically ok. Your query actually says "give me such salary for which there exists N-1 higher salaries". Anyway it looks too complex and difficult to understand. This is good case for using nth_value window function or at least order by ... limit ... offset, no nested subquery is needed here.

Upvotes: 3

Related Questions