sql - Multiple max values from joined tables -

i need write sql query employees highest pay each department separately , return name , pay of employee , department name 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  |        |     | 2  | marketing |     +----+-----------+ 

this query returns first employee each department highest pay in table, employees, have highest pay, in case both bob , dave , tom marketing.

  select d.name,e.name,e.pay employees e join departments d    on e.department_id = d.id group d.id having max(e.pay) 

the correct result should be:

+-----------+-------+------+ |        | bob   | 1200 | |        | dave  | 1200 | | marketing | tom   | 800  | +-----------+-------+------+ 

redefinition of "having maximum salary" is: "there should nobody higher salary" (within same department)

select d.name, e.name,e.pay employees e join departments d on e.department_id = d.id not exists (     select 42 employees x     x.department_id = e.department_id -- same dept     , x.pay > e.pay                       -- higher pay     );