Highest Salary in each department

Question

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

User · Answer

with ctesal as    select DepartmentId   Name   Salary  ROW Number   OVER  partition by DepartmentId   order by Salary desc  as RowNum  from dbo Employee     select DepartmentId   Name   Salary   RowNum from ctesal where RowNum   2    This is applicable to SQL server  ROW Number is a inbuilt function in SQL server  It gives count starting from 1 based on partition by and order by clause  At the end  We can write where condition based on our requirements

User · Answer

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

User · Answer

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

User · Answer

If you just want to get the highest salary from that table  by department   SELECT MAX Salary  FROM TableName GROUP BY DeptID

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

gt    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

As short as the question   SELECT DeptID  MAX Salary  FROM EmpDetails GROUP BY DeptID

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Use the below quesry   select employee name salary department id from emp where salary in select max salary  from emp group by department id

User · Answer

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

User · Answer

Use correlated subquery   SELECT DeptID  EmpName  Salary FROM EmpDetails a WHERE Salary    SELECT MAX Salary                   FROM EmpDetails b                 WHERE a DeptID   b DeptID

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I have like 2 approaches using one with Rank and the other with ROW NUMBER This is my sample data Age          Name                                               Gender     Salary ----------- -------------------------------------------------- ---------- ----------- 1           Mark                                               Male       5000 2           John                                               Male       4500 3           Pavan                                              Male       5000 4           Pam                                                Female     5500 5           Sara                                               Female     4000 6           Aradhya                                            Female     3500 7           Tom                                                Male       5500 8           Mary                                               Female     5000 9           Ben                                                Male       6500 10          Jodi                                               Female     7000 11          Tom                                                Male       5500 12          Ron                                                Male       5000 13          Ramani                                             Female     7000  So here is my first query to find max salary and the person with that max salary for each Gender     with CTE as      select RANK   over partition by Gender Order by Salary desc  as  Rank    from employees      select   from CTE where  Rank  1   Rank                 Age          Name                                               Gender     Salary -------------------- ----------- -------------------------------------------------- ---------- ----------- 1                    10          Jodi                                               Female     7000 1                    13          Ramani                                             Female     7000 1                    9           Ben                                                Male       6500  So in this case  we can see there is a tie between these 2 female employees  quot Jodi quot  and  quot Ramani quot   In that case  As a tie-breaker I want to make use of Age as a deciding factor and person with more age is supposed to be displayed with CTE as  select RANK   over partition by Gender Order by Salary desc age desc  as  Rank    from employees  select   from CTE where  Rank  1  Rank                 Age          Name                                               Gender     Salary -------------------- ----------- -------------------------------------------------- ---------- ----------- 1                    13          Ramani                                             Female     7000 1                    9           Ben                                                Male       6500  Usually  in this case for finding the highest salary  it doesn t make much difference even if Rank  Dense Rank  or Row Number   are used  But they have some impact in other cases

User · Answer

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

User · Answer

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

User · Answer

select T1   from   select empname as e1 department name salary from employee   as T1   select max salary  maxsal department name dept1 from  employee group by department name  as T2 where T1 department name T2 dept1 and T1 salary maxsal

[sql] Highest Salary in each department

Examples related to sql

Examples related to sql-server

Examples related to tsql