Click here to Skip to main content
15,302,644 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
I have a table Employee. I want to select Name,Department and Max salary in each department. Please help.

EmpID	 Name   Department	Salary
 1	         A	   IT	     5000
 2	         B	   IT	     4000
 3	         C	   QA	     4000
 4	         D	   SS	     8000
 5	         E	   IT	     10000
 6	         F	   QA	     4500
Posted
Comments
Thanks7872 30-Apr-15 8:09am
   
There is nothing to help you. What have you tried and what is the issue here?
fawad masood 30-Apr-15 8:27am
   
see the solution below
Richard Deeming 30-Apr-15 9:07am
   
Your question is tagged with "SQL 2000", "SQL 2008 R2", "SQL 2012" and "SQL 2014". In future, please only select the tag which matches the version you are using.

SQL
SELECT
    Employee.*
FROM Employee
INNER JOIN (
    SELECT
        Department,
        MAX(Salary) AS TopSalary
    FROM Employee
    GROUP BY
        Department) AS DepartmentSalaries
    ON Employee.Department = DepartmentSalaries.Department
    AND Employee.Salary = DepartmentSalaries.TopSalary
   
Comments
Maciej Los 30-Apr-15 8:58am
   
+5
Another way is to use ROW_NUMBER() ranking function:
SQL
SELECT EmpID, Name, Department, Salary
FROM (
   SELECT EmpID, Name, Department, Salary, ROW_NUMBER() OVER(Partition By Department ORDER BY Salary DESC) AS RowNo
   FROM Employee
) AS T
WHERE RowNo=1
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900