CODEWITHSUNDEEP
sqlsql-servergroup-by
Jason Clark
Jason Clark

Reputation: 1425

How to get the firstname and max salary of employee according to their department from this query?

How can i get the firstname from employeedetail table?

I am executing below command and getting max salary of a employee according to their department:

select department, max(salary) 
from employeedetail 
where salary in (
    select max(salary) 
    from employeedetail 
    group by department
) 
group by department

but when I am trying to return the firstname with the above query, it showing me below error message:

Msg 8120, Level 16, State 1, Line 1947 Column 'employeedetail.firstname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can i get the firstname ?

Upvotes: 0

Views: 915

Answers (2)

Ajay Dwivedi
Ajay Dwivedi

Reputation: 328

Below is one more variation of TSQL Code to achieve the same result.

SELECT  E.FirstName, S.*
FROM (  select  department, max(salary) as max_Salary
        from    employeedetail
        group by department
     ) S
CROSS APPLY
    (SELECT e.* FROM employeedetail AS e WHERE e.department = S.department AND e.Salary = S.max_Salary) AS E;

So choose your query by comparing IO & Time Stats of all queries in your environment. http://statisticsparser.com/ site can help in analyzing Query Statistics.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

You are getting this immediate error because you are trying to select a non aggregate column which does not appear in your GROUP BY clause. One canonical way of approaching this is to use a subquery which finds the maximum salaries for each department. Then, join your employeedetails table to this subquery to obtain the names of employees earning the highest salaries in each department.

SELECT t1.firstname, t1.department
FROM employeedetail t1
INNER JOIN
(
    SELECT department, MAX(salary) AS max_salary
    FROM employeedetail
    GROUP BY department
) t2
    ON t1.department = t2.department AND
       t1.salary     = t2.max_salary

Another way to approach this problem would be to use ROW_NUMBER(). This might be more useful if you wanted to resolve ties between two or more employees in the same department with regard to highest salary. Something like this would be an alternative:

SELECT t.firstname, t.salary
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) rn
    FROM employeedetail
) t
WHERE t.rn = 1

The usefulness of ROW_NUMBER() here is that we could handle ties in salary by adding additional criteria to the ORDER BY clause of the partition. For example, we could use the employees age/tenure to resolve a tie, giving preference to senior employees:

ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary, tenure DESC)

Upvotes: 3

Related Questions