view raw
IvanA930 IvanA930 - 7 months ago 40
SQL Question

Need to find top 3 employees

I working with two tables

Employee ( empid integer, mgrid integer, deptid integer, salary integer)
Dept (deptid integer, deptname text)

I need to find top 3 employees (salary based), in every department. Result should have deptname, empid, salary sorted by deptname and then employee with high to low salary.

I appreciate all your help !!


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
        SELECT d.deptname,
               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;