MySQL Top N Query: Highest Values, Ties and Top N per Group

“Who are the top 3 earners?”, “Which product is the most expensive?”, “The best-selling item in each category.” These are top-N queries.

query.sql

SQL

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

INSERT INTO employees (name, department, salary, manager_id, hired_on) VALUES
    ('Asha Rao',     'management',  150000.00, NULL, '2020-01-15'),
    ('Ben Carter',   'engineering', 120000.00,    1, '2021-03-01'),
    ('Chitra Nair',  'engineering',  95000.00,    2, '2022-07-11'),
    ('Dev Malhotra', 'engineering',  88000.00,    2, '2023-01-09'),
    ('Esha Gupta',   'sales',        70000.00,    1, '2021-09-20'),
    ('Farid Khan',   'sales',        64000.00,    5, '2022-11-02'),
    ('Gita Menon',   'sales',        64000.00,    5, '2024-02-14'),
    ('Hari Iyer',    'support',      52000.00,    1, '2023-05-30');

The simple version: ORDER BY and LIMIT

query.sql

SQL

SELECT name, salary FROM employees ORDER BY salary DESC, id LIMIT 3;

Output

OUTPUT

+-------------+-----------+
| name        | salary    |
+-------------+-----------+
| Asha Rao    | 150000.00 |
| Ben Carter  | 120000.00 |
| Chitra Nair |  95000.00 |
+-------------+-----------+
3 rows in set

The problem: ties

Look at the salaries around the bottom of the list:

query.sql

SQL

SELECT name, salary FROM employees ORDER BY salary DESC, id;

Output

OUTPUT

+--------------+-----------+
| name         | salary    |
+--------------+-----------+
| Asha Rao     | 150000.00 |
| Ben Carter   | 120000.00 |
| Chitra Nair  |  95000.00 |
| Dev Malhotra |  88000.00 |
| Esha Gupta   |  70000.00 |
| Farid Khan   |  64000.00 |
| Gita Menon   |  64000.00 |
| Hari Iyer    |  52000.00 |
+--------------+-----------+
8 rows in set

Farid Khan and Gita Menon both earn 64000. A “top 6” with LIMIT 6 would include Farid but cut Gita, and which of them is cut is decided by the tie-breaker (id). If both should appear, LIMIT is the wrong tool.

Include ties: filter by the Nth value

Find the salary at position 6, then keep everyone at or above it:

query.sql

SQL

SELECT name, salary
FROM employees
WHERE salary >= (SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 5)
ORDER BY salary DESC, id;

Output

OUTPUT

+--------------+-----------+
| name         | salary    |
+--------------+-----------+
| Asha Rao     | 150000.00 |
| Ben Carter   | 120000.00 |
| Chitra Nair  |  95000.00 |
| Dev Malhotra |  88000.00 |
| Esha Gupta   |  70000.00 |
| Farid Khan   |  64000.00 |
| Gita Menon   |  64000.00 |
+--------------+-----------+
7 rows in set

The subquery returns the 6th highest salary (64000). The outer query returns everyone who earns that or more, which includes both people with 64000: seven rows for a “top 6”. This is the equivalent of the standard FETCH FIRST 6 ROWS WITH TIES, which MySQL does not have.

Include ties with a window function

RANK() gives tied rows the same rank (Window Functions):

query.sql

SQL

SELECT name, salary, salary_rank
FROM (
    SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees
) AS ranked
WHERE salary_rank <= 6
ORDER BY salary_rank, name;

Output

OUTPUT

+--------------+-----------+-------------+
| name         | salary    | salary_rank |
+--------------+-----------+-------------+
| Asha Rao     | 150000.00 |           1 |
| Ben Carter   | 120000.00 |           2 |
| Chitra Nair  |  95000.00 |           3 |
| Dev Malhotra |  88000.00 |           4 |
| Esha Gupta   |  70000.00 |           5 |
| Farid Khan   |  64000.00 |           6 |
| Gita Menon   |  64000.00 |           6 |
+--------------+-----------+-------------+
7 rows in set

Both 64000 earners have rank 6.

The highest value per group

“The highest paid person in each department”. A common wrong attempt:

query.sql

SQL

SELECT department, name, MAX(salary) FROM employees GROUP BY department;

Output

OUTPUT

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'learn.employees.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

ONLY_FULL_GROUP_BY rejects it, since name is not tied to the maximum. (In old MySQL with that mode off, it returned a name, often the wrong one.) The right ways:

1. A join with the maximum per group:

query.sql

SQL

SELECT e.department, e.name, e.salary
FROM employees e
JOIN (SELECT department, MAX(salary) AS top_salary FROM employees GROUP BY department) m
  ON m.department = e.department AND m.top_salary = e.salary
ORDER BY e.department;

Output

OUTPUT

+-------------+------------+-----------+
| department  | name       | salary    |
+-------------+------------+-----------+
| engineering | Ben Carter | 120000.00 |
| management  | Asha Rao   | 150000.00 |
| sales       | Esha Gupta |  70000.00 |
| support     | Hari Iyer  |  52000.00 |
+-------------+------------+-----------+
4 rows in set

2. A window function (shortest and most flexible, and the one to use on MySQL 8):

query.sql

SQL

SELECT department, name, salary
FROM (
    SELECT department, name, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, id) AS rn
    FROM employees
) t
WHERE rn = 1
ORDER BY department;

Output

OUTPUT

+-------------+------------+-----------+
| department  | name       | salary    |
+-------------+------------+-----------+
| engineering | Ben Carter | 120000.00 |
| management  | Asha Rao   | 150000.00 |
| sales       | Esha Gupta |  70000.00 |
| support     | Hari Iyer  |  52000.00 |
+-------------+------------+-----------+
4 rows in set

Use ROW_NUMBER() for exactly one row per group, and RANK() to keep ties. For the top N per group, change rn = 1 into rn <= N.

Which value to use for “the Nth highest”?

query.sql

SQL

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;

Output

OUTPUT

+----------+
| salary   |
+----------+
| 95000.00 |
+----------+
1 row in set

DISTINCT makes the third highest distinct salary, which is 95000 here (150000, 120000, 95000). Without DISTINCT, duplicated values would count several times. This is a famous interview question (SQL Interview Questions).

lightbulb

Decide what you mean by ties

“Top 3” can mean exactly three rows, or the top three distinct values (with everyone who shares them). Choose before you write the query. Then use LIMIT, DENSE_RANK() or RANK() accordingly.