user457666 user457666 - 6 months ago 10
SQL Question

using MAX aggregate between two tables

I have two tables, employer and position:

Employer

eID

eName

Position

eID

salary

I need to match my eID between the two tables, determine what the max salary is, and print only the eName. Any suggestions as to how I can do this? I have tried multiple ways, but nothing seems to work.

I am not sure where to put in the max(salary) function:

select eName
from employer, position
where employer.eID = position.eID

Answer

To get the name(s) of the people with the highest salary...

Using a JOIN:

SELECT e.name
  FROM EMPLOYER e
  JOIN POSITION x ON x.eid = e.eid
  JOIN (SELECT MAX(salary) AS max_salary
          FROM POSITION) y ON y.max_salary = x.salary

Using a subquery:

SELECT e.name
  FROM EMPLOYER e
  JOIN POSITION p ON p.eid = e.eid
 WHERE p.salary = (SELECT MAX(salary)
                     FROM POSITION)