Member Avatar for king03

Hi there guys I have a question. How do I list names of employees whose salary is the highest for their position (among everyone with same position. I must include the whole name so that's first_name and last_name

the name of the table is: employees
here are the columns that I think will be necessary for the question: job_id, salary, first_name, last_name

here was my attempt, unfortunately it doesn't run:

select first_name, last_name, job_id, max(salary)
from employees
group by job_id;

please help me out guys, thank you very much.

Edited by king03 because: wrong
  • 2 Contributors
  • 1 Reply
  • 61 Views
  • 9 Minutes Discussion Span
  • Latest Post Latest Post by JorgeM
Member Avatar for JorgeM

Try this..

SELECT *
FROM Employees emp1
WHERE NOT EXISTS (
  SELECT 1 
  FROM Employees emp2
  WHERE emp1.job_id = emp2.job_id
  AND emp1.salary < emp2.salary
)
ORDER BY salary DESC
Edited by JorgeM
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.