0
**Department table name** 
**following with fields name**

 id , empid ,salary ,departid ,status

how to get the top two highest salaries from each department with single query in mysql

satyam
  • 123
  • 1
  • 7

3 Answers3

10
SELECT emp1.departid , emp1.salary
FROM Department emp1
WHERE (
SELECT COUNT(DISTINCT(emp2.salary))
FROM Department emp2
WHERE emp2.salary > emp1.salary and emp1.departid  = emp2.departid ) in (0,1) group by emp1.departid , emp1.salary

I have tested this solution in sql server. But i think it will work in ms sql as well.

Coder
  • 490
  • 4
  • 18
  • Allow me to explain! The magic is in the inner query: the "emp1.departid = emp2.departid" part limits comparison to be between same department salaries. The "emp2.salary > emp1.salary" finds all salaries greater the current salary (in emp1). Counting those ones gives you a number! if this number is 9, it means our salary is top 10th, if the count is 0 it means our salary has none smaller than it, so it's the top 1st! Smart solution, Coder! Thanks – salouri May 08 '18 at 12:00
  • so, it gives you all salaries of the department that has only 1, or none salaries higher than them in the same department. (if 0, it's the highest, if 1 then the salary is the second highest) – salouri May 08 '18 at 12:11
9

Try

SELECT id, empid, salary, departid, status
  FROM 
(
  SELECT id, empid, salary, departid, status, 
         @n := IF(@g = departid, @n + 1, 1) rownum,
         @g := departid
    FROM table1
   ORDER BY departid, salary DESC 
) q
 WHERE q.rownum <= 2

Here is SQLFiddle demo

What it does it generates in inner select a rank for each employee in each department based on the salary. Then in outer select it filters out all rows that have rank more than 2 (top two).

peterm
  • 91,357
  • 15
  • 148
  • 157
1

I believe this is what you want. Please notice that this query is not tested, since I don't have access to a MySQL db atm.

SELECT 
    d1.id,
    d1.salary,
    d1.departid
FROM department d
WHERE
    d1.id IN
    (
        SELECT
            d2.id
        FROM department d2
        WHERE
            d2.departid = d1.departid
        ORDER BY salary DESC
        LIMIT 2

    )

It's probably not the most efficient query, but it does the job.

MrSoundless
  • 1,246
  • 2
  • 12
  • 34