How to retrieve other fields when using GROUP BY

[KevenCook]

Imagine a table of name, dept, salary

I want the results to be: for each department, i want
to know what the max salary is and the name of the person with this salary.

i've found an example which suggest the following would work:

SELECT name, max(salary), dept
FROM employee 
GROUP BY dept, name

However this results in many results per department, the
GROUP BY seems to be looking for unique (dept, name) combinations.

If I omit 'name' from the GROUP BY it complains that each
field in the SELECT must be in an aggregate function or the GROUP BY clause.

Can what I need be done in SQL?

Keven.

 

[rac2]

Yes it can be done in SQL.

You are quite right in observing that the GROUP BY gives a result for every unique combination of column values. The solution requires two parts, a view that has the top salary for each department, and a query that joins this information with information about the employees with that salary.

Suppose the employee table has columns name, dept, salary, and etc.

First create the view.

CREATE VIEW top_salaries
AS
SELECT dept, max(salary) AS high_salary
FROM employee 
GROUP BY dept

Then the query to get all the info you want from the employee table for those top earners.

SELECT e.name, e.etc, ts.high_salary, e.dept
FROM top_salaries ts
JOIN employee e ON ts.dept = e.dept
  AND e.salary = ts.high_salary

Note that more than one department employee might earn the same high salary so you might get more than one employee per department.

Creating the view is convenient if you will need the top departmental salary in more than one report, and it makes the query easier to read. But you could get the same result in a single query.

SELECT e.name, e.etc, ts.high_salary, e.dept
FROM (SELECT dept, max(salary) AS high_salary
      FROM employee 
      GROUP BY dept) ts
JOIN employee e ON ts.dept = e.dept
  AND e.salary = ts.high_salary

 

[angiole]

Another approach that I've used successfully is (assuming lengths of 5 for each column.

Select dept,
substr(max(salary || name),1,5) as max_salary,
substr(max(salary || name),6,5) as max_salary_name
from Table1
group by dept

Please note as was mentioned in a previous comment that if there are 2 people with the same max salary, then one one will be displayed, in this case, the one with the highest alphabetic name.