Course: MySQL Training
Topic: Retrieving Top-N Records and Group-Wise Maximum Values
Course Link: MySQL Training Course
Demo Link: Register for a Free Course Demo
In MySQL, retrieving Top-N records and group-wise maximum values is an important SQL skill used to analyze, rank, compare, and summarize data. These techniques help developers and data analysts identify the highest-performing products, top-selling employees, most recent orders, highest salaries, and maximum values within different categories.
Top-N records refer to a specified number of records selected according to a sorting rule. For example, retrieving the five employees with the highest salaries means selecting the top five rows after sorting employees by salary in descending order.
Group-wise maximum values refer to the maximum value within each group of records. For example, finding the highest salary in every department means grouping employees by department and identifying the maximum salary for each department.
Although these tasks may look similar, they answer different questions. A Top-N query can return the highest salaries across an entire company, while a group-wise maximum query returns the highest salary for every department.
A Top-N query retrieves a specified number of records based on a particular ordering condition. The letter N represents the number of rows that should be returned.
For example, if a company has 1,000 employees and an analyst wants to find the ten highest-paid employees, a Top-N query can retrieve those ten employees without displaying all 1,000 records.
Top-N queries are commonly used for:
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name DESC
LIMIT N;Explanation:
| Clause | Purpose |
|---|---|
| SELECT | Specifies the columns to retrieve. |
| FROM | Specifies the source table. |
| ORDER BY | Sorts the records according to one or more columns. |
| DESC | Sorts values from highest to lowest, or newest to oldest for suitable date columns. |
| ASC | Sorts values from lowest to highest; this is the default order. |
| LIMIT N | Restricts the output to at most N rows. |
For a Top-N query, sorting should generally be applied before LIMIT. Without ORDER BY, the database does not guarantee that the returned rows represent the highest, lowest, newest, or oldest records.
To understand Top-N queries and group-wise maximum values, consider an employee table containing employee details, department names, and salaries.
CREATE DATABASE company_db;
USE company_db;CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10,2),
joining_date DATE
);INSERT INTO employees
(employee_id, employee_name, department, salary, joining_date)
VALUES
(1, 'Amit', 'IT', 75000, '2023-01-15'),
(2, 'Priya', 'HR', 60000, '2022-03-10'),
(3, 'Rahul', 'IT', 90000, '2021-07-20'),
(4, 'Sneha', 'Finance', 85000, '2020-11-05'),
(5, 'Vikas', 'HR', 65000, '2023-06-18'),
(6, 'Neha', 'IT', 90000, '2022-09-12'),
(7, 'Karan', 'Finance', 70000, '2021-04-25'),
(8, 'Meera', 'IT', 80000, '2024-02-01'),
(9, 'Arjun', 'HR', 65000, '2020-08-14'),
(10, 'Pooja', 'Finance', 95000, '2023-12-01');SELECT *
FROM employees
ORDER BY employee_id;The table contains employees from IT, HR, and Finance departments. Some employees have the same salary, which will help demonstrate how ties affect Top-N and group-wise maximum queries.
To find the five employees with the highest salaries, sort the salary column in descending order and limit the result to five rows.
SELECT employee_id, employee_name, department, salary
FROM employees
ORDER BY salary DESC, employee_id ASC
LIMIT 5;Explanation:
ORDER BY salary DESC places higher salaries before lower salaries.employee_id ASC provides a consistent secondary ordering when employees have the same salary.LIMIT 5 returns at most five records.The query returns five rows. If several employees have equal salaries near the fifth position, the secondary sort makes the selected rows deterministic, but it does not include every employee tied at the cutoff.
SELECT employee_name, salary
FROM employees
ORDER BY salary DESC, employee_id ASC
LIMIT 3;This query returns three employees ordered from the highest salary to the third-highest row. If more than one employee has the same salary, the employee ID determines their order.
Top-N logic can also be used to retrieve the lowest values by sorting in ascending order.
SELECT employee_name, department, salary
FROM employees
ORDER BY salary ASC, employee_id ASC
LIMIT 3;This query returns the three lowest-paid employees. The same approach can be used to identify the cheapest products, lowest examination marks, or smallest transactions.
For dates, descending order generally places the latest date first.
SELECT employee_name, department, joining_date
FROM employees
ORDER BY joining_date DESC, employee_id ASC
LIMIT 5;This query returns up to five employees with the most recent joining dates. The employee ID provides a consistent order when joining dates are identical.
A Top-N query can sort by more than one column. This is useful when the main value is equal for multiple records.
SELECT employee_name, department, salary, joining_date
FROM employees
ORDER BY salary DESC, joining_date ASC, employee_id ASC
LIMIT 5;The query prioritizes higher salaries. If salaries are equal, the earlier joining date comes first. If both salary and joining date are equal, employee ID determines the order.
OFFSET specifies how many rows should be skipped before MySQL starts returning the requested number of rows. It is commonly used to display data page by page in web applications, dashboards, and administrative systems.
SELECT column1, column2
FROM table_name
ORDER BY column_name DESC
LIMIT row_count OFFSET offset_value;SELECT employee_name, salary
FROM employees
ORDER BY salary DESC, employee_id ASC
LIMIT 3 OFFSET 3;This query skips the first three ordered rows and returns the next three rows, if available.
SELECT employee_name, salary
FROM employees
ORDER BY salary DESC, employee_id ASC
LIMIT 3, 3;In this syntax, the first number represents the offset and the second number represents the row count. Therefore, LIMIT 3, 3 skips three rows and returns the next three.
| Page | LIMIT Syntax | Rows Skipped |
|---|---|---|
| Page 1 | LIMIT 3 OFFSET 0 |
0 |
| Page 2 | LIMIT 3 OFFSET 3 |
3 |
| Page 3 | LIMIT 3 OFFSET 6 |
6 |
| Page 4 | LIMIT 3 OFFSET 9 |
9 |
Suppose the previous page ended with an employee whose salary was 80,000 and whose employee ID was 8. To continue after that position using the same ordering, use a condition that matches the sorting logic.
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary < 80000
OR (salary = 80000 AND employee_id > 8)
ORDER BY salary DESC, employee_id ASC
LIMIT 5;This example demonstrates the principle of keyset pagination. The comparison conditions must match the ORDER BY columns and their sort directions. For production queries, ensure that the cursor values and NULL-handling rules are defined correctly.
Use WHERE to filter the records before sorting and applying LIMIT.
SELECT employee_name, department, salary
FROM employees
WHERE department = 'IT'
ORDER BY salary DESC, employee_id ASC
LIMIT 3;This query selects only IT employees, sorts them by salary, and returns at most three records.
SELECT employee_name, department, salary
FROM employees
WHERE salary > 70000
ORDER BY salary DESC, employee_id ASC
LIMIT 5;The WHERE clause excludes employees whose salary is 70,000 or less. LIMIT is then applied to the remaining ordered records.
The same approach applies to product tables.
SELECT product_name, price
FROM products
WHERE price BETWEEN 1000 AND 10000
ORDER BY price DESC, product_id ASC
LIMIT 10;This query retrieves up to ten products within the specified price range, ordered from the most expensive to the least expensive. It assumes that the products table contains the specified columns.
Suppose two employees share the highest salary. A query using ORDER BY salary DESC LIMIT 1 returns only one row, even though two employees are tied for the highest salary.
Whether this is correct depends on the business requirement. Some reports require exactly N rows, while others require every record tied at the Nth position.
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
);This query returns every employee whose salary equals the maximum salary in the table. It is preferable to a single-row LIMIT query when all employees tied for first place must be included.
| Requirement | Recommended Approach | Result Behavior |
|---|---|---|
| Return exactly up to five rows | ORDER BY ... LIMIT 5 |
Returns at most five rows, regardless of ties. |
| Return all rows tied at the fifth row's value | Use a ranking function or compare against the Nth value. | May return more than five rows. |
| Return the top five distinct salary levels | Use DENSE_RANK() or select distinct salary values. |
May return more than five employees. |
| Return all employees with the highest salary | Use MAX() with a subquery or an appropriate ranking function. |
Returns every row tied for the maximum. |
MAX() is an aggregate function that returns the greatest non-NULL value in a set of values. It works with numeric values, dates, and other comparable data types.
Examples include:
SELECT MAX(column_name)
FROM table_name;SELECT MAX(salary) AS highest_salary
FROM employees;The result is a single value representing the maximum non-NULL salary. The query does not return the employee's name or other columns associated with that salary.
SELECT MAX(joining_date) AS latest_joining_date
FROM employees;For a DATE column, MAX() returns the latest non-NULL date. This can be useful for reporting the most recent registration, order, payment, or employee joining date.
SELECT MAX(price) AS highest_price
FROM products;This query returns the highest non-NULL product price. If every value is NULL or the table contains no rows, MAX() returns NULL.
A group-wise maximum query identifies the largest value within each group of records. Instead of finding one maximum across the entire table, the query calculates a separate maximum for each category.
For example, an organization may want to identify the highest salary in IT, the highest salary in HR, and the highest salary in Finance.
SELECT group_column, MAX(value_column) AS maximum_value
FROM table_name
GROUP BY group_column;The GROUP BY clause divides records into groups based on the specified column or columns. MAX() then calculates the maximum value separately for each group.
SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department
ORDER BY department;This query returns one row for each department value and its highest non-NULL salary. It returns the maximum salary amount, not the complete employee record associated with that amount.
The same grouping technique can be used with MIN() to retrieve the lowest salary within each department.
SELECT department, MIN(salary) AS lowest_salary
FROM employees
GROUP BY department
ORDER BY department;SELECT category_id, MAX(price) AS highest_price
FROM products
GROUP BY category_id
ORDER BY category_id;This query calculates the highest product price for every category. It assumes that the products table has category_id and price columns.
Sometimes a maximum must be calculated for each combination of values, such as department and job title.
SELECT department, job_title, MAX(salary) AS highest_salary
FROM employees
GROUP BY department, job_title
ORDER BY department, job_title;This query calculates a separate maximum salary for every department and job-title combination. It assumes that a job_title column exists in the table.
| Feature | MAX() Across a Table | MAX() with GROUP BY |
|---|---|---|
| Purpose | Finds one overall maximum value. | Finds a maximum for each group. |
| Number of result rows | Usually one row for an aggregate without GROUP BY. | One row per group produced by GROUP BY. |
| Example | Highest salary in the company. | Highest salary in every department. |
| Typical syntax | SELECT MAX(salary) FROM employees; |
SELECT department, MAX(salary) FROM employees GROUP BY department; |
| Returns the complete employee record? | No. | No. |
A common requirement is to retrieve not only the maximum salary for each department but also the employee ID, employee name, and other columns of every employee earning that salary.
A simple GROUP BY query is not enough to reliably return all the associated employee details. The following techniques solve this problem.
First, calculate the maximum salary for each department. Then match those maximum values against the original employee table.
SELECT e.employee_id,
e.employee_name,
e.department,
e.salary
FROM employees AS e
JOIN (
SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department
) AS d
ON e.department = d.department
AND e.salary = d.highest_salary
ORDER BY e.department, e.employee_id;How it works:
Important: In this example, departments are assumed to have non-NULL department values. If department can be NULL, ordinary equality does not match NULL to NULL; use a NULL-safe comparison such as e.department <=> d.department where appropriate.
If two employees in the same department share the highest salary, both rows match the department's maximum salary. This behavior is correct when the requirement is to return all employees tied for the maximum.
A correlated subquery calculates the maximum salary for the department associated with each outer employee.
SELECT e.employee_id,
e.employee_name,
e.department,
e.salary
FROM employees AS e
WHERE e.salary = (
SELECT MAX(e2.salary)
FROM employees AS e2
WHERE e2.department <=> e.department
)
ORDER BY e.department, e.employee_id;The inner query refers to the department of the outer employee. The NULL-safe equality operator, <=>, treats two NULL values as equal. Therefore, employees whose department is NULL are considered together as one group.
This query returns every employee whose salary equals the maximum non-NULL salary of their department. An employee with a NULL salary will not match the result of MAX() for a group containing only NULL salaries, because comparison with NULL does not evaluate to TRUE.
A derived table is a subquery placed in the FROM clause and assigned an alias. It is useful for calculating group summaries before joining them to detailed records.
SELECT e.employee_name,
e.department,
e.salary
FROM employees e
INNER JOIN (
SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department
) AS department_max
ON e.department <=> department_max.department
AND e.salary = department_max.max_salary
ORDER BY e.department, e.employee_name;This query uses NULL-safe equality for the department comparison. It includes all employees tied at the maximum salary for their department.
Window functions calculate values across related rows while keeping individual rows in the result. Unlike ordinary aggregate queries with GROUP BY, window functions can assign ranks to employees without collapsing each department into a single row.
MySQL 8.0 and later support ranking functions such as ROW_NUMBER(), RANK(), and DENSE_RANK(). These functions are particularly useful for Top-N-per-group queries and group-wise maximum problems.
WITH ranked_employees AS (
SELECT employee_id,
employee_name,
department,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC, employee_id ASC
) AS row_num
FROM employees
)
SELECT employee_id, employee_name, department, salary
FROM ranked_employees
WHERE row_num = 1
ORDER BY department;Explanation:
PARTITION BY department creates a separate ranking group for each department.ORDER BY salary DESC ranks higher salaries first within each department.employee_id ASC breaks salary ties deterministically.ROW_NUMBER() assigns a unique sequential number to each row within its partition.WHERE row_num = 1 returns exactly one selected employee per non-empty department partition.Because employee_id is used as a tie-breaker, the query deliberately selects one employee when several share the maximum salary. It does not return all tied employees.
WITH ranked_employees AS (
SELECT employee_id,
employee_name,
department,
salary,
RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS salary_rank
FROM employees
)
SELECT employee_id, employee_name, department, salary
FROM ranked_employees
WHERE salary_rank = 1
ORDER BY department, employee_id;RANK() gives equal salary values the same rank. If multiple employees share the highest salary in a department, every one of them receives rank 1 and is returned.
WITH ranked_employees AS (
SELECT employee_id,
employee_name,
department,
salary,
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS salary_rank
FROM employees
)
SELECT employee_id, employee_name, department, salary
FROM ranked_employees
WHERE salary_rank <= 3
ORDER BY department, salary DESC, employee_id;This query retrieves employees whose salaries fall within the three highest distinct salary levels in their respective departments. More than three employees may be returned for a department if multiple employees share those salary levels.
| Function | How Ties Are Handled | Typical Use |
|---|---|---|
| ROW_NUMBER() | Assigns a different number to each row, even when values tie. | Selecting exactly one row per group using a defined tie-breaker. |
| RANK() | Assigns equal ranks to ties and leaves gaps after tied positions. | Returning all records tied at a specified ranking position. |
| DENSE_RANK() | Assigns equal ranks to ties without leaving gaps. | Selecting the top N distinct values within each group. |
Consider these salaries ordered from highest to lowest: 100000, 90000, 90000, 80000.
| Salary | ROW_NUMBER() | RANK() | DENSE_RANK() |
|---|---|---|---|
| 100000 | 1 | 1 | 1 |
| 90000 | 2 | 2 | 2 |
| 90000 | 3 | 2 | 2 |
| 80000 | 4 | 4 | 3 |
When using ROW_NUMBER(), include enough columns in the window ORDER BY to determine a predictable order. If only salary is specified, the order among employees with equal salaries is not guaranteed.
Top N per group means retrieving a specified number of records from every group, rather than from the whole table. For example, a company may want the three highest-paid employees in every department.
A query such as ORDER BY salary DESC LIMIT 3 returns three employees across the entire result set. It does not return three employees per department.
WITH ranked_employees AS (
SELECT employee_id,
employee_name,
department,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC, employee_id ASC
) AS row_num
FROM employees
)
SELECT employee_id, employee_name, department, salary
FROM ranked_employees
WHERE row_num <= 3
ORDER BY department, salary DESC, employee_id ASC;This query ranks employees separately within each department and returns up to three rows per department. It returns fewer than three if a department contains fewer than three employees.
WITH ranked_employees AS (
SELECT employee_id,
employee_name,
department,
salary,
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS salary_rank
FROM employees
)
SELECT employee_id, employee_name, department, salary
FROM ranked_employees
WHERE salary_rank <= 3
ORDER BY department, salary DESC, employee_id;This version returns employees from the three highest distinct salary levels in each department. If several employees share a salary level, all of them are included, so the result can contain more than three rows per department.
The same technique works for orders. Assume an orders table contains order_id, customer_id, and order_date.
WITH ranked_orders AS (
SELECT order_id,
customer_id,
order_date,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC
) AS row_num
FROM orders
)
SELECT order_id, customer_id, order_date
FROM ranked_orders
WHERE row_num <= 2
ORDER BY customer_id, order_date DESC, order_id DESC;This query retrieves up to two recent orders for each customer. The order_id tie-breaker makes the ordering consistent when multiple orders have the same date. The example assumes the orders table contains the listed columns.
One common interview question asks for the second-highest salary. The meaning should be clarified: usually, this means the second-highest distinct non-NULL salary, not the salary of the second row after sorting.
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);The inner query finds the highest salary. The outer query excludes values equal to that maximum and selects the greatest remaining salary. If no second distinct non-NULL salary exists, the result is NULL.
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1;This query returns the second-highest distinct non-NULL salary. DISTINCT is essential because repeated salaries should count as one salary level.
WITH salary_ranks AS (
SELECT employee_id,
employee_name,
salary,
DENSE_RANK() OVER (
ORDER BY salary DESC
) AS salary_rank
FROM employees
WHERE salary IS NOT NULL
)
SELECT employee_id, employee_name, salary
FROM salary_ranks
WHERE salary_rank = 2;This query returns every employee earning the second-highest distinct salary. Unlike a query that returns only the salary amount, it also returns the employee details.
Suppose salaries are 100000, 100000, 90000, and 80000. The second row after sorting has salary 100000, but the second-highest distinct salary is 90000. Understanding this difference prevents incorrect results in reports and interviews.
SELECT department, MAX(salary) AS highest_salary
FROM employees
WHERE salary > 70000
GROUP BY department
ORDER BY department;The WHERE clause filters employee rows before grouping. Therefore, the maximum is calculated only among employees whose salary exceeds 70,000.
SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > 80000
ORDER BY highest_salary DESC;HAVING filters the groups after aggregate values have been calculated. This is different from WHERE, which filters individual rows before grouping.
| Feature | WHERE | HAVING |
|---|---|---|
| Filtering stage | Filters rows before grouping and aggregation. | Filters groups after grouping and aggregation. |
| Typical use | Select employees with salary greater than 70,000 before calculating maximum salary. | Select departments whose maximum salary exceeds 80,000. |
| Aggregate conditions | Generally cannot directly filter using an aggregate calculated over the same query level. | Can filter using aggregate expressions such as MAX(salary). |
MAX() ignores NULL values when determining the maximum. If a group contains salaries 50000, NULL, and 70000, the maximum is 70000. If all salaries in a group are NULL, the maximum for that group is NULL.
SELECT department
FROM employees
GROUP BY department
HAVING MAX(salary) IS NULL;This query identifies department groups with no non-NULL salary values. It assumes that department is used to define the groups, including a possible NULL department group.
SELECT employee_id, employee_name, department
FROM employees
WHERE salary IS NULL;Use IS NULL to test for NULL values. Comparisons such as salary = NULL do not work as expected because NULL represents an unknown or missing value.
Suppose a store has products and order_items tables. The products table contains product_id and product_name. The order_items table contains product_id, quantity, and unit_price.
SELECT p.product_id,
p.product_name,
SUM(oi.quantity) AS total_units_sold
FROM products AS p
JOIN order_items AS oi
ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
ORDER BY total_units_sold DESC, p.product_id ASC
LIMIT 5;This query joins product details with order-item records, totals the quantity sold per product, and returns the five products with the greatest total sales volume.
SELECT p.product_id,
p.product_name,
SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM products AS p
JOIN order_items AS oi
ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
ORDER BY total_revenue DESC, p.product_id ASC
LIMIT 5;This query ranks products by calculated revenue rather than units sold. If discounts, returns, taxes, or refunds apply, the revenue formula must reflect the business rules and actual schema.
SELECT p.product_id,
p.product_name,
p.category_id,
p.price
FROM products AS p
JOIN (
SELECT category_id, MAX(price) AS max_price
FROM products
GROUP BY category_id
) AS category_max
ON p.category_id <=> category_max.category_id
AND p.price = category_max.max_price
ORDER BY p.category_id, p.product_id;This query returns all products tied for the maximum price in their category. The NULL-safe comparison handles a possible NULL category_id. Rows with NULL prices do not match a non-NULL maximum, and categories with only NULL prices will not produce matching product rows.
Indexes can help MySQL locate and sort relevant records more efficiently. The most useful index depends on the query, table size, data distribution, and execution plan.
For example, if the application frequently retrieves the highest salaries, an index beginning with salary may help:
CREATE INDEX idx_employees_salary
ON employees (salary);For queries that filter by department and sort employees by salary, a composite index may be more suitable:
CREATE INDEX idx_employees_department_salary
ON employees (department, salary DESC, employee_id);MySQL can use indexes differently depending on the query and storage engine. A descending index key part is supported in modern MySQL versions, but whether this index improves a particular query should be confirmed with EXPLAIN and realistic data.
Retrieving every column can increase data transfer and may prevent a query from being satisfied entirely by an index. Select only the columns required by the application or report.
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC, employee_id ASC
LIMIT 10;EXPLAIN
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC, employee_id ASC
LIMIT 10;EXPLAIN displays information about how MySQL plans to execute a query. Review index usage, estimated rows, join order, and whether sorting or temporary-table operations may be required.
Sorting large datasets can be expensive. Apply selective WHERE conditions where appropriate, use suitable indexes, and return only the required columns. Do not remove ORDER BY from a Top-N query merely to make it faster, because doing so changes the meaning of the result.
Queries with a large OFFSET may require MySQL to process many rows before returning the requested page. For large datasets, keyset pagination can be more efficient when a stable ordering key is available.
Index recommendations should be tested against actual data and query plans. An index is not guaranteed to improve performance, and additional indexes increase storage requirements and the cost of INSERT, UPDATE, and DELETE operations.
| Problem | Possible Cause | Solution |
|---|---|---|
| Top-N query returns unexpected records. | ORDER BY is missing or uses the wrong sort direction. | Specify the correct ORDER BY expression before LIMIT. |
| Only one employee is returned when several share the highest salary. | LIMIT 1 returns one row, not all ties. | Use MAX() with a subquery or RANK() to return all tied employees. |
| GROUP BY returns the maximum salary but not the employee name. | Aggregate results do not automatically identify the corresponding row. | Join the grouped maximum values to the original table or use a window function. |
| Top three per department returns only three rows overall. | LIMIT was applied to the whole result set. | Use ROW_NUMBER() with PARTITION BY department. |
| Second-highest salary is incorrect. | Duplicate salaries were counted as separate positions. | Use DISTINCT, MAX() with a subquery, or DENSE_RANK(). |
| Some records disappear from maximum-value results. | NULL values or NULL group keys are not handled correctly. | Check NULL handling and use NULL-safe comparisons when appropriate. |
| Pagination repeats or skips records. | Sorting is not unique or records change between page requests. | Use a deterministic sort order and consider keyset pagination. |
| Query performance is slow. | Large scans, sorting, joins, or unsuitable indexes. | Inspect EXPLAIN and evaluate appropriate indexes using realistic data. |
| Window-function query produces a syntax error. | The MySQL version may not support the function or CTE syntax. | Use MySQL 8.0 or later for the examples with CTEs and window functions, or rewrite using subqueries and joins. |
| Grouped query produces an error about nonaggregated columns. | Selected columns are not included in GROUP BY or an aggregate expression. | Group by the required columns or restructure the query using a join or window function. |
Write a query to retrieve the five highest salaries from the employees table, showing employee ID, employee name, and salary.
Write a query to retrieve the three employees with the lowest salaries. If salaries tie, use employee_id to make the ordering predictable.
Write a query to display each department and its highest salary.
Write a query that returns the employee name, department, and salary for every employee whose salary equals the maximum salary in their department.
Use ROW_NUMBER() to retrieve at most two employees per department, with higher salaries ranked first.
Use DENSE_RANK() to return employees whose salaries fall within the two highest distinct salary levels of their departments.
Write a query that returns the second-highest distinct non-NULL salary. Decide how the query should behave if fewer than two distinct salary values exist.
Use GROUP BY, MAX(), and HAVING to display only departments whose highest salary exceeds 80,000.
Assume an orders table with order_id, customer_id, and order_date. Use ROW_NUMBER() to return the latest order for each customer.
Assume products and order_items tables. Write a query that calculates total revenue per product and returns the five highest-revenue products.
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC, employee_id ASC
LIMIT 5;SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary ASC, employee_id ASC
LIMIT 3;SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department
ORDER BY department;SELECT e.employee_id, e.employee_name, e.department, e.salary
FROM employees AS e
JOIN (
SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department
) AS d
ON e.department <=> d.department
AND e.salary = d.highest_salary
ORDER BY e.department, e.employee_id;WITH ranked_employees AS (
SELECT employee_id, employee_name, department, salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC, employee_id ASC
) AS row_num
FROM employees
)
SELECT employee_id, employee_name, department, salary
FROM ranked_employees
WHERE row_num <= 2
ORDER BY department, row_num;WITH ranked_employees AS (
SELECT employee_id, employee_name, department, salary,
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS salary_rank
FROM employees
WHERE salary IS NOT NULL
)
SELECT employee_id, employee_name, department, salary
FROM ranked_employees
WHERE salary_rank <= 2
ORDER BY department, salary DESC, employee_id;SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > 80000
ORDER BY highest_salary DESC;WITH ranked_orders AS (
SELECT order_id, customer_id, order_date,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC
) AS row_num
FROM orders
)
SELECT order_id, customer_id, order_date
FROM ranked_orders
WHERE row_num = 1
ORDER BY customer_id;SELECT p.product_id,
p.product_name,
SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM products AS p
JOIN order_items AS oi
ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
ORDER BY total_revenue DESC, p.product_id ASC
LIMIT 5;A Top-N query retrieves up to a specified number of rows according to a sorting condition. It commonly uses ORDER BY and LIMIT.
SELECT employee_name, salary
FROM employees
ORDER BY salary DESC, employee_id ASC
LIMIT 5;A group-wise maximum identifies the maximum value separately for each group, such as the highest salary in each department.
SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department;No. It returns the group and the maximum value. To retrieve the associated employee details, join the maximum values back to the original table or use an appropriate window function.
Calculate the maximum salary per department and join it to the original employee table, matching both department and salary. Alternatively, use RANK() partitioned by department and filter for rank 1.
LIMIT restricts the number of rows returned by a query. GROUP BY combines rows into groups for aggregate calculations. LIMIT does not automatically apply separately to every group.
ROW_NUMBER() assigns a distinct sequence number to each row. RANK() assigns equal ranks to tied values and leaves gaps after ties.
DENSE_RANK() is useful when the requirement concerns the top N distinct values. It assigns the same rank to equal values without skipping the next rank.
You can use MAX() with a subquery that excludes the overall maximum, or use DISTINCT with descending ORDER BY and LIMIT with OFFSET.
WHERE filters rows before grouping, while HAVING filters groups after aggregate calculations.
Without ORDER BY, MySQL does not guarantee which rows will be returned. A Top-N result needs an explicit ordering rule.
Yes. MAX() returns NULL when there are no non-NULL values to aggregate, such as when a table is empty or every value in the relevant group is NULL.
Use ROW_NUMBER() with PARTITION BY department and filter for row numbers less than or equal to three. Use DENSE_RANK() instead if the requirement is the top three distinct salary levels.
The examples using ROW_NUMBER(), RANK(), DENSE_RANK(), and common table expressions require MySQL 8.0 or later.
Retrieving Top-N records and group-wise maximum values is essential for extracting meaningful information from relational databases. Top-N queries use ordering and row limits to return a selected number of records, while group-wise maximum queries calculate the highest value separately for each category or group.
For simple ranking tasks, ORDER BY and LIMIT are effective. For maximum values, MAX() and GROUP BY provide concise aggregate results. When complete rows are required, subqueries and joins connect the maximum values to their original records. For advanced ranking tasks, MySQL 8.0 window functions such as ROW_NUMBER(), RANK(), and DENSE_RANK() provide flexible solutions for selecting one row, preserving ties, or retrieving the top N distinct value levels within each group.
Always clarify whether the requirement means exactly N rows, all rows tied at a cutoff, or the top N distinct values. This distinction, combined with correct NULL handling, deterministic ordering, and performance testing, helps produce accurate and reliable SQL queries.
