Wenzel745 Wenzel745 - 1 month ago 6
SQL Question

Select max of a column based on another column ID

I am trying to get the maximum salary for each year in a table with the attributes teamID, salary, yearID. Shouldn't be hard right? Here is my query:

SELECT teamID, MAX(tS.teamSalary), yearID
FROM
(SELECT
teamID,
sum(salary) AS teamSalary,
yearID
FROM salaries
GROUP BY teamID, yearID) tS
GROUP BY yearID;


The inner query works just fine, but the outer one is just reporting the first teamID for each group. What am I doing wrong?

Inner query output:
A 1 2000
B 1 2000
C 2 2000
A 2 2001
B 3 2001
A 2 2002
B 2 2002

Full query output:
A 1 2000
A 2 2001
A 2 2002

Desired output:
C 2 2000
B 3 2001
A 2 2002

Answer

First you get maxSalary by year, then you get the extra information:

SELECT teamID, salary, yearID
  FROM salaries 
  JOIN
    (SELECT MAX(salary) AS maxSalary,
            yearID
       FROM salaries
   GROUP BY yearID) tS
    ON ts.yearID = salaries.yearID
   AND ts.maxSalary = salaries.salary

EDIT: Not sure if you want the max salary of some year, then to which team it belongs, or if you want the max salary by team and year. The second option is here:

  SELECT MAX(salary) AS maxSalary, yearID, teamID
    FROM salaries
GROUP BY yearID, teamID
Comments