IvanA930 - 2 months ago 12

SQL Question

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

Answer

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

Source (Stackoverflow)

Comments