How to Select the Top 3 Salaries of the Department

How to select the top 3 salaries of the department?

If you added a column Count employees who earn more your table would look like this

+----+-------+--------+--------------+-------------------------------+
| Id | Name  | Sa1ary | DepartmentId | Count employees who earn more |
+----+-------+--------+--------------+-------------------------------+
|  1 | Joe   |  70000 |            1 |    2                          |
|  2 | Henry |  80000 |            2 |    0                          |
|  3 | Sam   |  60000 |            2 |    1                          | 
|  4 | Max   |  90000 |            1 |    0                          |
|  5 | Janet |  69000 |            1 |    3                          |
|  6 | Randy |  85000 |            1 |    1                          |
+----+-------+--------+--------------+-------------------------------+

Then to find the top 3 per dept. your WHERE would be

WHERE `Count employees who earn more` < 3

If you had =3 it would return the only employees that was the 4th highest

Since you don't have that column, that's what this SQL does

(SELECT COUNT(DISTINCT(Salary)) FROM Employee 
       WHERE DepartmentId = E.DepartmentId AND Salary > E.Salary)

If you wanted to produce the table described above you could do the following

SELECT 
      D.Name AS Department, 
      E.Name AS Employee, 
      E.Salary AS Salary,
      Count(E2.Salary) as Count_employees_who_earn_more
FROM Employee E 
INNER JOIN Department D 
ON E.DepartmentId = D.Id 
LEFT JOIN Employee E2 ON 
    e2.DepartmentId = E.DepartmentId
    AND E2.Salary > E.Salary
GROUP BY  D.Name , 
      E.Name , 
      E.Salary 

Demo

Find the 3rd Maximum Salary for each department based on table data

Select EmpID,empname,deptid,salary
 From (
Select *
      ,RN  = Row_Number() over (Partition By deptid Order By Salary)
      ,Cnt = sum(1) over (Partition By deptid)
 From  employee1
      ) A
 Where RN = case when Cnt<3 then Cnt else 3 end

Returns

How to find top three highest salary in emp table in oracle?

SELECT  *FROM 
    (
    SELECT *FROM emp 
    ORDER BY Salary desc
    )
WHERE rownum <= 3
ORDER BY Salary ;

Need to find top 3 employees

There are two parts in this query. The inner and outer query.

First part, I am partitioning at deptid level and assigning the rank to the salaries in the descending order which solves your problem.
Second part is just a select on all the output fields by filtering out only the top three employees in each department.

Key point :- Assigning rank to the salaries for each department using ROW_NUMBER window function in the inner query solves the entire problem.

SELECT deptname,empid,salary
  FROM
     (
        SELECT d.deptname,
               e.empid,
               e.salary,
               ROW_NUMBER() OVER ( PARTITION BY e.deptid ORDER BY 
               e.salary DESC ) AS rank_salary_by_dept
          FROM dept d, employee e
         WHERE d.deptid = e.deptid
      )
WHERE rank_salary_by_dept <= 3
ORDER BY deptname,rank_salary_by_dept;

---

Related Topics