CODEWITHSUNDEEP
mysqlsql
Modaresi
Modaresi

Reputation: 233

COUNT with RANK mysql

From my database I want to get the lastname of employee, the number of employees who earn higher than him, and then rank the employee's salary in descending order; with the highest earning employee as zero. Here is how I have done it:

SELECT 0+COUNT(b.salary) rank
, a.lname
, a.salary
, COUNT(*) AS employee_count
FROM employee a LEFT OUTER JOIN employee b
ON a.salary<b.salary
GROUP BY a.salary, a.lname
ORDER BY salary DESC

And the result is shown like this:

enter image description here

So far so good. Except, Borg shouldn't be even there. Because no one is earning more than him. Now this happened because of using LEFT OUTER JOIN. I tried using INNER JOIN but it gives syntax error. So the question is how can I make INNER JOIN work with this?

Upvotes: 4

Views: 338

Answers (1)

Russell Fox
Russell Fox

Reputation: 5445

If you just want to eliminate the top value:

SELECT *
FROM (    
    SELECT 0+COUNT(b.salary) rank
        , a.lname
        , a.salary
        , COUNT(*) AS employee_count
    FROM employee a 
    LEFT OUTER JOIN employee b
        ON a.salary < b.salary
    GROUP BY a.salary, a.lname
    ORDER BY salary DESC
) T1
WHERE T1.rank > 0

Upvotes: 1

Related Questions