group by with another row aggregate function-CodePudding

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

CodePudding user response：

PostgreSQL has a nifty distinct on syntax you can use:

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

SQLFiddle demo

CodePudding user response：

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