Get difference in top 2 salary of employee from each department

Question

I have a simple employee table where i need to find difference between max of two salary in each department with maximum rank. Table columns i have: dept,sal,rank

Sample data could be

Dept        Sal     Rank
Finance     10000   1
Finance     20000   2
Finance     11000   1
Finance     15000   3
Finance     45000   4
Finance     42000   4
Operations  17000   1
Operations  12000   1
Operations  15000   2
Operations  19000   2

Desired output is

Dept        Diff    Rank
Finance     3000    4
Operations  4000    2

I have managed to fetch top 2 record in a very very inefficient way. I am using mysql server.

Here is the query

SELECT *
FROM   emps s
WHERE 
        (
            SELECT  COUNT(*) 
            FROM    emps  f
            WHERE f.dept = s.dept AND 
                  f.rank >= s.rank
        ) <= 2

I need further help to get the output.

See the added tag. Or see http://mysql.rjweb.org/doc.php/groupwise_max — Rick James, Oct 19 '17 at 21:12

score 2 · Accepted Answer · answered Oct 19 '17 at 07:05

You can give the following query a try:

SELECT Dept, MAX(Rank) AS Rank,
       SUM(CASE 
              WHEN rnk = 1 THEN Sal 
              WHEN rnk = 2 THEN -Sal 
              ELSE 0
           END) AS diff
FROM (
   SELECT @rnk := IF(@dept = Dept, @rnk + 1,
                     IF(@dept := Dept, 1, 1)) AS rnk,
          Dept, Sal, Rank                  
   FROM emp
   CROSS JOIN (SELECT @rnk := 0, @dept = '') AS vars
   ORDER BY Dept, Rank DESC, Sal DESC) AS t
GROUP BY Dept

The query uses variables to assign a rank number to each record depending on Rank and Sal values. The outer query consumes the variable values and performs conditional aggregation to calculate the difference between the first and the second ranking records.

Demo here

thank you. I have no idea how this particular query worked. But i will and debug every line of it asap to understand. If required will bug you again. Thanks again. — Abhishek, Oct 19 '17 at 07:29
would you please explain whats happening here ... its amazing — N-Alpr, Oct 19 '17 at 07:37

Get difference in top 2 salary of employee from each department

1 Answers1