How to find the top 2 salaried employees in each department

Let us take the table having different departments

Table Name:-  employee

+------+------------+------------+--------------+
| id   | dept_name  |  emp_name  |      salary  |
+------+------------+------------+--------------+
|    1 | ECE        | Raj        |        10000 |
|    2 | ECE        | Ravi       |        12000 |
|    3 | ECE        | Malli      |        14000 |
|    4 | CSE        | Madhav     |        12000 |
|    5 | CSE        | Jaani      |        14000 |
|    6 | CSE        | Jack       |        15000 |
|    7 | IT         | Mill       |        16000 |
|    8 | IT         | Pawan      |        17000 |
|    9 | IT         | Jack       |        18000 |
+------+------------+------------+--------------+

From above table for top 2 salaried employees see below query


SELECTc.*, d.ranknum
FROM employee AS c
INNER JOIN (
SELECT a.id, COUNT(*) AS ranknum
FROM employee AS a
INNER JOIN employee AS b ON (a.`dep_name` = b.`dep_name`) AND (a.`salary` <= b.`salary`)
GROUP BY a.id
HAVING COUNT(*) <= 2
) AS d ON (c.id = d.id)
ORDER BY c.`dep_name`, d.ranknum

Output for above query:

+------+------------+------------+--------------+---------------+
| id   | dept_name  |  emp_name  |      salary  | ranknum       |
+------+------------+------------+--------------+---------------+
|    6 | CSE        | Jack       |        15000 |         1     |
|    5 | CSE        | Jaani      |        14000 |         2     |
|    3 | ECE        | Malli      |        14000 |         1     |
|    2 | ECE        | Ravi       |        12000 |         2     |
|    9 | IT         | Jacks      |        18000 |         1     |
|    8 | IT         | Pawan      |        17000 |         2     |
+------+------------+------------+--------------+---------------+