Multiple max values from joined tables

Question

I need to write SQL query that will get the employees with the highest pay from each department separately and return name and pay of the employee and the department name they are in.

    employees
    +----+-------+------+---------------+
    | id | name  | pay  | department_id |
    +----+-------+------+---------------+
    | 1  | Bob   | 1200 | 1             |
    | 2  | Rob   | 600  | 2             |
    | 3  | Tom   | 800  | 2             |
    | 4  | Pam   | 900  | 1             |
    | 5  | Dave  | 1200 | 1             |
    +----+-------+------+---------------+

    departments
    +----+-----------+
    | id | name      |
    +----+-----------+
    | 1  | IT        |
    | 2  | Marketing |
    +----+-----------+

This query returns just the first employee from each department with the highest pay in the table, but I would like to get all the employees, that have the highest pay, in this case both Bob and Dave from IT and Tom from Marketing.

  SELECT d.name,e.name,e.pay FROM employees e JOIN departments d 
  ON e.department_id = d.id GROUP BY d.id HAVING MAX(e.pay)

The correct result should be:

+-----------+-------+------+
| IT        | Bob   | 1200 |
| IT        | Dave  | 1200 |
| Marketing | Tom   | 800  |
+-----------+-------+------+

score 2 · Accepted Answer · answered Apr 25 '15 at 13:32

Redefinition of "having the maximum salary" is: "there should be nobody with a higher salary" (within the same department)

SELECT d.name, e.name,e.pay
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE NOT EXISTS (
    SELECT 42 FROM employees x
    WHERE x.department_id = e.department_id -- same dept
    AND x.pay > e.pay                       -- higher pay
    );

score 0 · Answer 2 · answered Apr 25 '15 at 13:20

0

Requires a subquery, this works with most DBs but don't know about MySQL.

SELECT
  d.name,
  e.name,
  e.pay
FROM
  employees e JOIN departments d
    ON e.department_id = d.id
WHERE
  (d.id, e.pay) =
    (SELECT
       d.id,
       MAX(e.pay)
     FROM
       employees e JOIN departments d
       ON e.department_id = d.id
     GROUP BY
       d.id)

answered Apr 25 '15 at 13:20

Scott Sosna

1,443
1
8
8

Why would you mention MySQL when this question is about Postgres? – Erwin Brandstetter Apr 25 '15 at 13:41

starko · Answer 3 · 2015-04-25T14:00:52.620

0

Try this

SELECT d.name,e.name,e.pay FROM employess e JOIN departments d 
  ON e.deparment_id = d.id where e.pay in (select max(pay) from employess group by id)

And SQL Fiddle

edited Apr 25 '15 at 14:00

answered Apr 25 '15 at 13:29

starko

1,150
11
26

`group by id` is wrong. And why the fiddle for MySQL? – Erwin Brandstetter Apr 25 '15 at 13:57
I change this to Postgre. – starko Apr 25 '15 at 14:01
[The name is *PostgreSQL* or *Postgres*](https://wiki.postgresql.org/wiki/Identity_Guidelines) or even just *pg* if you need it short. Never "Postgre". You wouldn't say "My" either, would you? And you missed the more important part, where `group by id` is wrong. – Erwin Brandstetter Apr 25 '15 at 14:07

score 0 · Answer 4 · edited May 23 '17 at 11:58

There is also the window function rank() for that purpose exactly:

SELECT d.name AS department, e.name AS person, e.pay
FROM  (
   SELECT department_id AS id, name, pay
        , rank() OVER (PARTITION BY department_id ORDER BY pay DESC NULLS LAST) AS rnk
   FROM   employees
   ) e
JOIN   departments d USING (id)
WHERE  rnk = 1;

SQL Fiddle.

NULLS LAST is only needed if pay can be NULL:

PostgreSQL sort by datetime asc, null first?

Aside: "name" and "id" are hardly useful identifiers as you can see from this simple query. I have to use column aliases to do anything useful.

Multiple max values from joined tables

4 Answers4