How to select the max salary per department, including the employees that earn it .
The sub query join will yield the highest salary in each department, then join to the output and get the employees for each department with the department's highest salary. This query will also return matches where there is a tie for highest paid employee within a department.
SELECT emp.empid, emp.deptid, emp.empName, emp.salary, dpt.deptName
FROM Employee AS emp
INNER JOIN Department AS dpt ON emp.deptid = dpt.deptid
INNER JOIN
(SELECT deptid, MAX(salary) AS maxsalary
FROM Employee
GROUP BY deptid) AS empMax ON emp.deptid = empMax.deptid
AND emp.salary = empMax.maxsalary;
row_number() function:-
If you want to find the maximum salary along with the employees, then use the ANSI standard row_number() function:
select d.deptno, e.ename, d.dname, e.sal, d.loc
from (select e.*, row_number() over (partition by deptno order by sal desc) as seqnum
from employees e
) e join
departments d
on e.deptno = d.deptno
where seqnum = 1
dense_rank()
select d.deptno, e.ename, d.dname, e.sal, d.loc
from
(
select e.ename,
e.sal,
e.deptno,
dense_rank() over (partition by deptno order by sal desc) as salRank
from employees e
) e
inner join departments d
on e.deptno = d.deptno
where salRank = 1;
Normal
select
d.DeptID,
max(e.Salary)
from
department d
inner join employees e on d.DeptID = e.DeptID
group by
d.DeptID;
SQL>
select de.deptno,
e.ename,
de.dname,
e.sal,
de.loc
from employees e
inner join
(
select max(e.sal) MaxSalary, d.deptno, d.loc, d.dname
from employees e
inner join departments d
on e.deptno = d.deptno
group by d.deptno, d.loc, d.dname
) de
on e.sal = de.MaxSalary
and e.deptno = de.deptno
order by de.deptno
