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 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;