How to select the max salary per department,including the employees that earn.

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