Highest Salary in each department

问题:

I have a table EmpDetails:

DeptID      EmpName   Salary
Engg        Sam       1000
Engg        Smith     2000
HR          Denis     1500
HR          Danny     3000
IT          David     2000
IT          John      3000

I need to make a query that find the highest salary for each department.

回答1:

As short as the question:

SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID

回答2:

Assuming SQL Server 2005+

WITH cteRowNum AS (
    SELECT DeptID, EmpName, Salary,
           DENSE_RANK() OVER(PARTITION BY DeptID ORDER BY Salary DESC) AS RowNum
        FROM EmpDetails
)
SELECT DeptID, EmpName, Salary
    FROM cteRowNum
    WHERE RowNum = 1;

回答3:

Select empname,empid,Sal,DeptName from 
(Select e.empname,e.empid,Max(S.Salary) Sal,D.DeptName, ROW_NUMBER() Over(partition by D.DeptName order by s.salary desc) Rownum
from emp e inner join Sal S
on e.empid=s.empid 
inner join Dept d on e.Deptid=d.Deptid
group by e.empname,e.empid,D.DeptName,s.Salary
) x where Rownum = 1

回答4:

SELECT empName,empDept,EmpSalary
FROM Employee
WHERE empSalary IN
  (SELECT max(empSalary) AS salary
   From Employee
   GROUP BY EmpDept)

回答5:

If you want to show other parameters too along with DeptId and Salary like EmpName, EmpId

SELECT 
        EmpID 
      , Name, 
      , Salary
      , DeptId 
   FROM Employee 
   where 
     (DeptId,Salary) 
     in 
     (select DeptId, max(salary) from Employee group by DeptId)

回答6:

Use the below quesry:

select employee_name,salary,department_id from emp where salary in(select max(salary) from emp group by department_id);

回答7:

ermn, something like:

select 
   d.DeptID,
   max(e.Salary)
from
   department d
   inner join employees e on d.DeptID = e.DeptID
group by
  d.DeptID

回答8:

SELECT empname
FROM empdetails
WHERE salary IN(SELECT deptid max(salary) AS salary
FROM empdetails
group by deptid)

回答9:

WITH cteRowNum AS (
    SELECT DeptID, EmpName, Salary,
           ROW_NUMBER() OVER(PARTITION BY DeptID ORDER BY Salary DESC) AS RowNum
        FROM EmpDetails
)
SELECT DeptID, EmpName, Salary,Rownum
    FROM cteRowNum
    WHERE RowNum in(1,2);

回答10:

select a.*
 from EmpDetails a
 inner join 
 (
 select DeptID,max(Salary) as Salary
 from EmpDetails group by DeptID  
 )b 
on a.DeptID = b.DeptID and a.Salary = b.Salary

回答11:

SELECT Employee_ID
     , First_name
     , last_name
     , department_id
     , Salary 
FROM (SELECT Employee_ID
           , First_name
           , last_name
           , department_id
           , Salary
           , MAX(salary) OVER (PARTITION BY department_id) dept_max_sal
      FROM EMPLOYEES) AS Emp
WHERE salary = dept_max_sal;

回答12:

This will work if the department, salary and employee name are in the same table.

select ed.emp_name, ed.salary, ed.dept from
(select max(salary) maxSal, dept from emp_dept group by dept) maxsaldept
inner join emp_dept ed
on ed.dept = maxsaldept.dept and ed.salary = maxsaldept.maxSal

Is there any better solution than this?

回答13:

Use following command;

SELECT  A.*
    FROM    @EmpDetails A
            INNER JOIN ( SELECT DeptID ,
                                MAX(salary) AS salary
                         FROM   @EmpDetails
                         GROUP BY DeptID
                       ) B ON A.DeptID = B.DeptID
                              AND A.salary = B.salary
    ORDER BY A.DeptID

回答14:

SELECT
    DeptID,
    Salary
FROM
    EmpDetails
GROUP BY
    DeptID
ORDER BY
    Salary desc

回答15:

SELECT D.DeptID, E.EmpName, E.Salary
FROM Employee E
INNER JOIN Department D ON D.DeptId = E.DeptId
WHERE E.Salary IN (SELECT MAX(Salary) FROM Employee);

回答16:

***

> /*highest salary by each dept*/

***
select d.Dept_Name,max(e.salary)
    from emp_details as e join Dept_Details as d
    on e.d_id=d.Dept_Id
    group by  d.Dept_Name

select  distinct e.d_id,d.Dept_Name
    from emp_details as e join Dept_Details as d 
    on e.d_id=d.Dept_Id

select  e.salary,d.Dept_Name,d.Dept_Id
    from emp_details as e join Dept_Details as d 
    on e.d_id=d.Dept_Id

/////simplest query for max salary dept_wise////

回答17:

This is the best possible solution for ORACLE:

Select * from (select customerid, city, freight,
row_number() over (partition by customerid order by freight desc) Row_Number from 
(select orders.orderId, customers.CUSTOMERID, customers.city, orders.FREIGHT from orders inner join customers on orders.customerid = customers.customerid where customers.country='Germany' order by customers.customerid, orders.freight desc) 
order by customerid, freight desc) where Row_Number<=2;

Notice here I have used partition by clause for marking row number, this is majorly because we need to partition the records grouping them according to customer id. I have used two inner queries here. The inner most query is to give a view which is sorted according to customer ID and decreasing order of cost. Now from that we need to obtain always top two records so firstly we need to name them and then we need to filter them according to rownum. Second level query is to mark rownum according to customer ID. And final query will filter the result according to rownum. For every partition.

回答18:

Here is a way to get maximum values and names on any version of SQL.

Test Data:

CREATE TABLE EmpDetails(DeptID VARCHAR(10), EmpName VARCHAR(10), Salary DECIMAL(8,2))
INSERT INTO EmpDetails VALUES('Engg','Sam',1000)
INSERT INTO EmpDetails VALUES('Engg','Smith',2000)
INSERT INTO EmpDetails VALUES('HR','Denis',1500)
INSERT INTO EmpDetails VALUES('HR','Danny',3000)
INSERT INTO EmpDetails VALUES('IT','David',2000)
INSERT INTO EmpDetails VALUES('IT','John',3000)

Example:

SELECT ed.DeptID
      ,ed.EmpName
      ,ed.Salary
FROM (SELECT DeptID, MAX(Salary) MaxSal
      FROM EmpDetails
      GROUP BY DeptID)AS empmaxsal
INNER JOIN EmpDetails ed
  ON empmaxsal.DeptID = ed.DeptID
 AND empmaxsal.MaxSal = ed.Salary

Not the most elegant, but it works.

回答19:

SELECT DeptID, MAX(Salary)
 FROM EmpDetails
GROUP BY DeptID

This query will work fine, but the moment if you want to fetch some others details related to the employee having the highest salary will contradict. You can use :

SELECT DepatID, a , b, c
 FROM EmpDetails
 WHERE Salary IN (
    SELECT max(Salary)
      FROM EmpDetails
     GROUP BY DeptID
 );

if you will use the previous query it will only reflects the records of the min val except the salary as you have used the max function.

回答20:

select empno 
from EMP e 
where salary=(select max(sal) 
              from EMP w 
              where  groupby w.deptno having e.deptno=w.deptno)

I hope it will work...

回答21:

Use correlated subquery:

SELECT DeptID, EmpName, Salary
FROM EmpDetails a
WHERE Salary = (SELECT MAX(Salary) 
                FROM EmpDetails b
                WHERE a.DeptID = b.DeptID)

回答22:

The below query will display employee name with their respective department name in which that particular employee name is having highest salary.

with T as
(select empname, employee.deptno, salary
from employee
where salary in (select max(salary)
from employee
group by deptno))
select empname, deptname, salary
from T, department
where T.deptno=department.deptno;

I executed the above query successfully on Oracle database.

回答23:

If you just want to get the highest salary from that table, by department:

SELECT MAX(Salary) FROM TableName GROUP BY DeptID

回答24:

select deptid, empname, salary from
(Select deptid, empname,salary,
rank() Over(Partition by deptid  order by salary desc)as rank from 
EmpDetails) emp 
where emp.rank = 1

First ranks each employee by salary in descending order having highest rank 1 and then selects only deptid, empname, salary. You can do this for all Nth member of the group.

回答25:

select * from (
    select a.* from EmpDetails a 
    right join (select DeptID,max(salary) as Salary from EmpDetails group by DeptID) b
    on b.DeptID=a.DeptID and b.salary=a.salary ) as c  group by c.DeptID;

回答26:

IF you want Department and highest salary, use

SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID

if you want more columns in employee and department, use

select  Department.Name , emp.Name, emp.Salary from Employee emp
inner join (select DeptID, max(salary) [salary] from employee group by DeptID) b
on emp.DeptID = b.DeptID and b.salary = emp.Salary
inner join Department on emp.DeptID = Department.id
order by Department.Name

if use salary in (select max(salary...)) like this, one person have same salary in another department then it will fail.

回答27:

The below listed query will list highest salary in each department.

select deptname, max(salary) from department, employee where 
  department.deptno=employee.deptno group by deptname;

I executed this query successfully on Oracle database.

回答28:

Not sure why no one has mentioned the Group By .... Having syntax. It specifically addresses these requirements.

select EmpName,DeptId,Salary from EmpDetails group by DeptId having Salary=max(Salary);