group by with another row aggregate function

Question

I am trying to get the max salary from the department column but I also want to know the person in that certain department.

what can I do here?

create table if not exists employee(
    id serial unique,
    firstName varchar (15),
    lastName varchar(15),
    department varchar (20),
    salary int
);

select department, max(salary) from employee
group by department

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Stu, Dec 14 '21 at 17:08
@LukStorms the same principle applies, in fact the accepted answer almost identical to yours :) — Stu, Dec 14 '21 at 17:25
Unrelated to your problem, but: Postgres 9.4 is [no longer supported](https://www.postgresql.org/support/versioning/) you should plan an upgrade as soon as possible. — , Dec 14 '21 at 17:31

score 2 · Answer 1 · answered Dec 14 '21 at 17:12

2

PostgreSQL has a nifty distinct on syntax you can use:

SELECT   DISTINCT ON (department) * 
FROM     employee
ORDER BY department ASC, salary DESC

SQLFiddle demo

answered Dec 14 '21 at 17:12

Mureinik

297,002
52
306
350

score 1 · Accepted Answer · answered Dec 14 '21 at 17:11

Calculate a rank that sorts by descending salary.
The top 1 will have rank 1 then.

select department, salary, firstName, lastName
from
(
    select department, salary, firstName, lastName
    , dense_rank() over (partition by department order by salary desc) as Rnk
    from employee 
) q
where Rnk = 1

group by with another row aggregate function

2 Answers2