List employees earning the maximum salary
Table employees(first_name, hire_date, salary). List the first name of every employee whose salary equals the maximum salary in the table. There may be ties, so return all of them, not just one row.
-- write a SELECT over employees that returns first_name
-- for every employee earning the maximum salaryWrite the query.
Compute the maximum salary once and select everyone matching it. A CTE makes it clean: WITH m AS (SELECT MAX(salary) s FROM employees) SELECT first_name FROM employees JOIN m ON salary = m.s. Equivalently WHERE salary = (SELECT MAX(salary) FROM employees).
- ✗Mixing MAX() with a plain column without GROUP BY
- ✗Using LIMIT 1, which drops tied top earners
- ✗Confusing above-average with the maximum
- →How would you also return only those hired this year?
- →Why can LIMIT 1 give a wrong answer here?
Compute the maximum salary once, then select everyone whose salary equals it so ties are preserved.
WITH max_salary AS (
SELECT MAX(salary) AS max_sal FROM employees
)
SELECT e.first_name, e.hire_date
FROM employees e
JOIN max_salary m ON e.salary = m.max_sal;An equivalent scalar-subquery form:
SELECT first_name, hire_date
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);ORDER BY salary DESC LIMIT 1 is wrong when several employees share the top salary — it returns only one of them. To also restrict to those hired this year, add AND hire_date >= date_trunc('year', current_date).
