You have 2 minutes to solve this SQL query.
Find the employee or employees with the highest salary in the company without using MAX().
Me: Challenge accepted!
SELECT
employee_id,
employee_name,
salary
FROM (
SELECT
employee_id,
employee_name,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
) ranked
WHERE salary_rank = 1;
Explanation:
This query finds the highest-paid employee or employees without using the MAX() aggregate function.
• DENSE_RANK() ranks salaries in descending order.
• The highest salary receives a rank of 1.
• The outer query filters only employees with salary_rank = 1.
• If multiple employees share the highest salary, they are all returned.
This question tests your understanding of:
• Window Functions using DENSE_RANK
• Ranking Data
• Handling Ties
• Alternatives to Aggregate Functions
Expected Output Example
Employee: John, Salary: 120,000
Employee: Alice, Salary: 120,000
Both employees are returned because they share the highest salary.
Alternative Solution using NOT EXISTS
SELECT
employee_id,
employee_name,
salary
FROM employees e1
WHERE NOT EXISTS (
SELECT 1
FROM employees e2
WHERE e2.salary > e1.salary
);
This solution works by returning employees for whom no other employee has a higher salary.
Tip for SQL Job Seekers:
Interviewers often ask you to solve problems without using aggregate functions like MAX() or MIN(). Learn multiple approaches using:
• Window Functions
• NOT EXISTS
• Correlated Subqueries
• Self Joins
Demonstrating alternative solutions shows strong SQL problem-solving skills.
❤️ React with ❤️ for more SQL interview challenges!