Often in MySQL, you may want to find the complete row that contains the maximum value for each category or group in your table.

For example, imagine you have a table of employee salaries across different departments, and you want to find the highest-paid employee in each department. Or perhaps you’re tracking product prices over time and need to find the most recent price for each product.

Let’s dive into this problem and explore several solutions, from simple to advanced.

First, let’s create a sample employee table to work with. We’ll use this table in a query to find the employee with the highest salary in each department, showing all their details (not just the department and max salary).

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

INSERT INTO employees (name, department, salary, hire_date) VALUES
    ('Alice', 'Engineering', 95000, '2020-01-15'),
    ('Bob', 'Engineering', 120000, '2019-03-20'),
    ('Charlie', 'Engineering', 85000, '2021-06-10'),
    ('Diana', 'Sales', 75000, '2020-08-05'),
    ('Eve', 'Sales', 82000, '2019-11-12'),
    ('Frank', 'HR', 65000, '2021-02-28'),
    ('Grace', 'HR', 70000, '2020-05-17'),
    ('Henry', 'Marketing', 78000, '2019-07-22'),
    ('Ivy', 'Marketing', 72000, '2021-09-03');

Our Goal: Find the employee with the highest salary in each department, showing all their details (not just the department and max salary).

Common Mistake: The GROUP BY Trap

Many developers first try something like this:

-- ❌ This doesn't work as expected!
SELECT name, department, MAX(salary) as max_salary
FROM employees
GROUP BY department;

Why this fails: MySQL will give you the MAX salary per department, but the name field will be picked arbitrarily from the group. You won’t necessarily get the name of the person with the maximum salary!

Solution 1: Using Subquery with JOIN (Most Reliable)

This is the most straightforward and reliable approach that works across all MySQL versions:

SELECT e.*
FROM employees e
INNER JOIN (
    SELECT department, MAX(salary) as max_salary
    FROM employees
    GROUP BY department
) as dept_max
ON e.department = dept_max.department 
AND e.salary = dept_max.max_salary;

How it works:

1. The subquery finds the maximum salary for each department
2. We join the original table back to these results
3. We match on both department AND salary to get the complete row

Results:

| id | name  | department  | salary   | hire_date  |
|----|-------|-------------|----------|------------|
| 2  | Bob   | Engineering | 120000.00| 2019-03-20 |
| 5  | Eve   | Sales       | 82000.00 | 2019-11-12 |
| 7  | Grace | HR          | 70000.00 | 2020-05-17 |
| 8  | Henry | Marketing   | 78000.00 | 2019-07-22 |

Pros:

• Works in all MySQL versions
• Easy to understand
• Reliable results

Cons:

• Can be slow on large tables (requires scanning twice)
• If multiple employees have the same max salary, you’ll get multiple rows per department

Solution 2: Using Correlated Subquery

Another approach uses a correlated subquery in the WHERE clause:

SELECT e1.*
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

How it works:

1. For each row in the outer query, we run a subquery
2. The subquery finds the MAX salary for that row’s department
3. We only keep rows where the salary matches the maximum

Pros:

• Clean, readable syntax
• Works across all SQL databases

Cons:

• Can be slower on large datasets (subquery runs for each row)
• Returns multiple rows if there are ties

Solution 3: Using Window Functions (MySQL 8.0+)

If you’re using MySQL 8.0 or later, window functions provide the most elegant solution:

SELECT id, name, department, salary, hire_date
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn
    FROM employees
) as ranked
WHERE rn = 1;

How it works:

1. PARTITION BY department creates separate groups for each department
2. ORDER BY salary DESC sorts within each group
3. ROW_NUMBER() assigns 1 to the highest salary in each group
4. We filter to only keep rows where rn = 1

Alternative with RANK():

If you want to include ties (multiple employees with the same max salary), use RANK() instead:

SELECT id, name, department, salary, hire_date
FROM (
    SELECT *,
           RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rnk
    FROM employees
) as ranked
WHERE rnk = 1;

Pros:

• Most efficient for large datasets
• Very readable once you understand window functions
• Flexible (easy to get top N per group)

Cons:

• Only works in MySQL 8.0+
• Slightly more complex concept to learn

Solution 4: Using LEFT JOIN (Exclusive Pattern)

This clever technique uses a LEFT JOIN to find rows with no “better” match:

SELECT e1.*
FROM employees e1
LEFT JOIN employees e2
  ON e1.department = e2.department
  AND e1.salary < e2.salary
WHERE e2.id IS NULL;

How it works:

1. We try to join each row to another row in the same department with a higher salary
2. If no such row exists (e2.id IS NULL), that means our row has the maximum salary
3. We keep only those rows

Pros:

• No subqueries needed
• Can be efficient with proper indexes

Cons:

• Less intuitive logic
• Can be slower without proper indexing

Handling Ties: What if Multiple Rows Have the MAX Value?

In all the solutions above, if two employees in the same department have the same highest salary, you’ll get both rows. If you want only one row per group, you have several options:

Option 1: Add a Tiebreaker

-- Get the employee with max salary, and if tied, the most recently hired
SELECT e.*
FROM employees e
INNER JOIN (
    SELECT department, MAX(salary) as max_salary
    FROM employees
    GROUP BY department
) as dept_max
ON e.department = dept_max.department 
AND e.salary = dept_max.max_salary
INNER JOIN (
    SELECT department, salary, MAX(hire_date) as latest_hire
    FROM employees
    GROUP BY department, salary
) as dept_latest
ON e.department = dept_latest.department
AND e.salary = dept_latest.salary
AND e.hire_date = dept_latest.latest_hire;

Option 2: Use ANY_VALUE() with MySQL 5.7+

-- Pick any employee with max salary (non-deterministic)
SELECT 
    department,
    ANY_VALUE(name) as name,
    MAX(salary) as salary
FROM employees
GROUP BY department;

Performance Considerations

When working with large tables, consider these optimization tips:

1. Add Indexes

-- Index on the grouping column
CREATE INDEX idx_department ON employees(department);

-- Composite index for better performance
CREATE INDEX idx_dept_salary ON employees(department, salary DESC);

2. Use EXPLAIN to Analyze

EXPLAIN SELECT e.*
FROM employees e
INNER JOIN (
    SELECT department, MAX(salary) as max_salary
    FROM employees
    GROUP BY department
) as dept_max
ON e.department = dept_max.department 
AND e.salary = dept_max.max_salary;

3. For Very Large Tables

If you’re dealing with millions of rows, window functions (MySQL 8.0+) will generally perform best:

-- Most efficient for large datasets
SELECT id, name, department, salary, hire_date
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn
    FROM employees
) as ranked
WHERE rn = 1;

Real-World Examples

Example 1: Latest Order Per Customer

SELECT o.*
FROM orders o
INNER JOIN (
    SELECT customer_id, MAX(order_date) as latest_order
    FROM orders
    GROUP BY customer_id
) as latest
ON o.customer_id = latest.customer_id
AND o.order_date = latest.latest_order;

Example 2: Highest-Rated Product Per Category

SELECT p.*
FROM products p
WHERE p.rating = (
    SELECT MAX(p2.rating)
    FROM products p2
    WHERE p2.category = p.category
);

Example 3: Most Recent Log Entry Per User

-- Using window function (MySQL 8.0+)
SELECT user_id, action, timestamp
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp DESC) as rn
    FROM activity_logs
) as ranked
WHERE rn = 1;

Summary: Which Method Should You Use?

Use the JOIN with subquery method when:

• You need compatibility with older MySQL versions
• Your dataset is small to medium-sized
• You prefer straightforward, easy-to-understand queries

Use window functions when:

• You’re on MySQL 8.0 or later
• You’re working with large datasets
• You need to get top N per group (not just top 1)
• Performance is critical

Use correlated subqueries when:

• You need simple, readable code
• Performance isn’t the primary concern
• You’re comfortable with subquery concepts

Practice Exercise

Try this on your own: Given the employees table above, write a query to find the employee with the earliest hire date in each department. Use at least two different methods!

Conclusion

Finding rows with maximum values per group is a fundamental SQL skill that you’ll use constantly in real applications. While the problem seems simple at first, the various solutions each have their trade-offs in terms of readability, performance, and compatibility.

Start with the JOIN method for reliability, then graduate to window functions once you’re comfortable with the concept and have MySQL 8.0+. Understanding all these approaches will make you a more versatile database developer!