Course: MySQL Training
Topic: Finding the Second-Highest and Nth-Highest Values Using SQL Queries
Course Link: MySQL Training Course
Demo Link: Register for a Free Course Demo
Finding the second-highest and Nth-highest values is a common SQL interview question and an important database querying skill. In MySQL, these problems frequently involve identifying the second-highest salary in an employee table, the third-highest product price, the fifth-highest sales amount, or another ranked value from a dataset.
There are several ways to solve these problems, including using MAX(), subqueries, DISTINCT, ORDER BY, LIMIT, and window functions such as DENSE_RANK() and ROW_NUMBER(). The appropriate method depends on whether duplicate values should count as separate positions and which MySQL version is being used.
For example, consider the following employee salaries:
| Employee ID | Employee Name | Salary |
|---|---|---|
| 1 | Amit | 90000 |
| 2 | Neha | 75000 |
| 3 | Rahul | 60000 |
| 4 | Priya | 75000 |
| 5 | Vikas | 50000 |
The highest salary is 90000. The second-highest distinct salary is 75000, even though two employees receive that salary. The third-highest distinct salary is 60000.
LIMIT and OFFSET to retrieve ranked values.ROW_NUMBER(), RANK(), and DENSE_RANK().The highest value is the maximum value in a column. For a salary column, it represents the largest salary recorded in the table.
SELECT MAX(salary) AS highest_salary
FROM employees;Explanation:
MAX(salary) returns the largest non-NULL salary.AS highest_salary assigns a readable name to the result column.The second-highest value is commonly understood as the second-largest distinct value. If salaries are 90000, 75000, 75000, 60000, and 50000, the second-highest distinct salary is 75000, not another occurrence of 90000 or 75000.
However, some questions ask for the second row after sorting, where duplicate values may occupy separate positions. Always clarify the expected meaning before selecting a query.
The Nth-highest value is the value at position N when distinct values are sorted in descending order, unless the problem explicitly specifies row-based ranking.
| Rank | Distinct Salary |
|---|---|
| 1st highest | 90000 |
| 2nd highest | 75000 |
| 3rd highest | 60000 |
| 4th highest | 50000 |
When distinct values are required, duplicate salaries count only once in the ranking.
Before practicing these queries, create a database table containing employee information.
CREATE DATABASE company_db;
USE company_db;CREATE DATABASE creates a database, while USE selects it for subsequent operations.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10, 2)
);Column descriptions:
| Column | Purpose |
|---|---|
| employee_id | Unique identifier for each employee. |
| employee_name | Name of the employee. |
| department | Department to which the employee belongs. |
| salary | Employee salary, stored as a decimal number. |
INSERT INTO employees
(employee_id, employee_name, department, salary)
VALUES
(1, 'Amit', 'IT', 90000),
(2, 'Neha', 'HR', 75000),
(3, 'Rahul', 'IT', 60000),
(4, 'Priya', 'Finance', 75000),
(5, 'Vikas', 'Sales', 50000),
(6, 'Sneha', 'IT', 45000),
(7, 'Karan', 'Finance', 40000);SELECT *
FROM employees
ORDER BY salary DESC;The ORDER BY salary DESC clause sorts the salaries from highest to lowest. The resulting order helps visualize the rankings, but it does not automatically remove duplicate salaries.
A subquery is a query placed inside another SQL query. One of the simplest ways to find the second-highest distinct salary is to find the maximum salary that is smaller than the overall maximum.
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);SELECT MAX(salary) FROM employees, finds the highest salary.MAX(salary) returns the largest value among those remaining salaries.For the sample data, the highest salary is 90000. The largest salary below 90000 is 75000, so the query returns 75000.
This query is specifically designed for the second-highest value. It does not directly generalize to every Nth-highest value without adding more nested queries or changing the approach.
MySQL supports LIMIT and OFFSET for selecting a limited number of rows from a result set. Combined with DISTINCT and descending sorting, they provide a convenient way to find ranked distinct values.
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1;DISTINCT salary removes duplicate salary values.WHERE salary IS NOT NULL excludes missing salaries.ORDER BY salary DESC sorts salaries from highest to lowest.OFFSET 1 skips the first distinct salary.LIMIT 1 returns the next salary only.The result is 75000 for the sample data.
| Clause | Meaning | Example |
|---|---|---|
LIMIT 1 OFFSET 0 |
Return the first row. | Highest value |
LIMIT 1 OFFSET 1 |
Skip one row and return the next. | Second-highest value |
LIMIT 1 OFFSET 2 |
Skip two rows and return the next. | Third-highest value |
LIMIT 1 OFFSET 3 |
Skip three rows and return the next. | Fourth-highest value |
MySQL also supports the comma-separated form of LIMIT:
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1, 1;In this form, the first number is the offset and the second number is the row count. Although valid, LIMIT 1 OFFSET 1 is often easier to read.
Advantages:
LIMIT.Limitations:
DISTINCT, duplicate values can occupy multiple positions.To find the Nth-highest distinct salary, skip the first N-1 distinct salaries and return one row.
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET N_MINUS_1;N_MINUS_1 is a placeholder, not a literal MySQL keyword. Replace it with the required numeric offset.
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 2;Result: 60000.
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 3;Result: 50000.
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 4;Result: 45000.
For a positive integer N:
The general relationship is:
OFFSET = N - 1
LIMIT = 1If N is 1, the offset is 0. If N is 2, the offset is 1. If N is 5, the offset is 4.
DENSE_RANK() is a window function that assigns a rank to each row based on the ordering of values. Rows with equal values receive the same rank, and the next distinct value receives the next consecutive rank.
Window functions such as DENSE_RANK() are available in MySQL 8.0 and later.
WITH ranked_salaries 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 ranked_salaries
WHERE salary_rank = 2;ranked_salaries.DENSE_RANK() assigns the same rank to employees with equal salaries.Because Neha and Priya both earn 75000, both employees receive rank 2. The query returns both records.
WITH ranked_salaries 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 ranked_salaries
WHERE salary_rank = 3;Changing salary_rank = 3 to another positive integer retrieves employees at that distinct salary rank.
ROW_NUMBER() assigns a unique sequential number to every row based on the specified ordering. Unlike DENSE_RANK(), it does not assign the same number to rows with equal salaries.
WITH numbered_employees AS (
SELECT
employee_id,
employee_name,
salary,
ROW_NUMBER() OVER (
ORDER BY salary DESC, employee_id ASC
) AS row_num
FROM employees
WHERE salary IS NOT NULL
)
SELECT employee_id, employee_name, salary
FROM numbered_employees
WHERE row_num = 2;The query sorts by salary in descending order and uses employee ID as a tie-breaker. If two employees have the same salary, their order is determined by employee ID.
WITH numbered_employees AS (
SELECT
employee_id,
employee_name,
salary,
ROW_NUMBER() OVER (
ORDER BY salary DESC, employee_id ASC
) AS row_num
FROM employees
WHERE salary IS NOT NULL
)
SELECT employee_id, employee_name, salary
FROM numbered_employees
WHERE row_num = 3;This query returns the third row in the sorted employee list, not necessarily the third-highest distinct salary.
RANK() is another window function. Equal values receive the same rank, but the next rank is skipped according to the number of tied rows.
For example, if salaries are 90000, 75000, 75000, and 60000, the rankings are:
| Salary | ROW_NUMBER() | RANK() | DENSE_RANK() |
|---|---|---|---|
| 90000 | 1 | 1 | 1 |
| 75000 | 2 | 2 | 2 |
| 75000 | 3 | 2 | 2 |
| 60000 | 4 | 4 | 3 |
WITH ranked_employees AS (
SELECT
employee_id,
employee_name,
salary,
RANK() OVER (
ORDER BY salary DESC
) AS salary_rank
FROM employees
WHERE salary IS NOT NULL
)
SELECT employee_id, employee_name, salary
FROM ranked_employees
WHERE salary_rank = 2;This query returns all employees with rank 2. In this example, both employees earning 75000 are included.
Both functions assign the same rank to tied values. The difference appears after a tie:
RANK() leaves gaps in the ranking.DENSE_RANK() does not leave gaps.For the Nth-highest distinct salary, DENSE_RANK() is usually the most direct window-function solution.
| Method | Best Use | Duplicate Handling | MySQL Compatibility |
|---|---|---|---|
MAX() with subquery |
Second-highest value | Finds the next distinct value when using less-than comparison | Works in older versions |
DISTINCT with LIMIT |
Second or Nth distinct value | Duplicates removed | Works in older versions |
DENSE_RANK() |
Nth-highest distinct rank and all matching records | Same value gets same rank; no gaps | MySQL 8.0+ |
RANK() |
Competition-style ranking | Ties share rank; gaps can appear | MySQL 8.0+ |
ROW_NUMBER() |
Nth row in a sorted result | Every row gets a unique number | MySQL 8.0+ |
Duplicate values are one of the most important considerations when solving ranking problems. The correct query depends on whether you need distinct values or individual rows.
| Employee | Salary |
|---|---|
| Amit | 100000 |
| Neha | 90000 |
| Rahul | 90000 |
| Priya | 80000 |
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1;Result: 90000.
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC, employee_id ASC
LIMIT 1 OFFSET 1;This returns one employee from the second sorted position. When the highest salary is unique, this will be an employee with the second-highest salary. If the highest salary occurs multiple times, the second row may have the same salary as the highest row.
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
)
);This returns every employee whose salary equals the second-highest distinct salary.
Sometimes the requirement is not simply to return a salary value. You may need the employee ID, name, department, and salary of every employee who earns the Nth-highest distinct salary.
WITH ranked_employees AS (
SELECT
employee_id,
employee_name,
department,
salary,
DENSE_RANK() OVER (
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 employee_id;This query returns all employee records at the second-highest distinct salary rank.
SELECT employee_id, employee_name, department, salary
FROM employees
WHERE salary = (
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1
);The inner query identifies the second-highest distinct salary. The outer query retrieves all employees whose salary matches that value.
In an application or stored procedure, the requested rank may be supplied dynamically. A MySQL session variable can represent the desired rank when used with a query that supports the chosen syntax.
SET @n = 3;
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 2;The example uses a fixed offset of 2 for rank 3. MySQL does not allow a variable to be substituted for every SQL syntax element in all contexts, so applications should validate N and calculate the offset carefully before constructing a query. Where supported by the application driver, use parameterized SQL for data values and validate any rank used in a LIMIT/OFFSET clause.
The requested rank should be a positive integer. A rank of 0 or a negative rank does not represent a valid Nth-highest value.
-- Example validation concept:
-- N must be an integer greater than or equal to 1.
SET @n = 3;
-- Offset is N - 1.
SET @offset_value = @n - 1;For application code, validate the value before running the query. Do not accept arbitrary SQL fragments from user input.
In MySQL, NULL represents a missing or unknown value. Aggregate functions such as MAX() ignore NULL values. However, sorting and ranking queries should make the intended treatment of NULL explicit.
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1;If all salaries are NULL, MAX(salary) returns NULL, and there is no valid second-highest salary. A query using DISTINCT and filtering out NULL values returns no rows.
A table may not contain enough distinct values to satisfy the requested rank. For example, if the distinct salaries are only 90000 and 75000, a third-highest distinct salary does not exist.
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 4;If there are fewer than five distinct non-NULL salary values, this query returns an empty result set.
If an application requires one result row even when the requested rank does not exist, a scalar subquery can return NULL when there is no matching value.
SELECT (
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1
) AS second_highest_salary;If no second-highest distinct salary exists, the scalar subquery produces NULL.
Some problems ask for the second-highest salary in each department instead of across the entire company. This requires partitioning the ranking by department.
WITH department_salary_ranks 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 department_salary_ranks
WHERE salary_rank = 2
ORDER BY department, employee_id;PARTITION BY department divides employees into department groups.ORDER BY salary DESC sorts salaries within each department.DENSE_RANK() starts ranking at 1 for each department.salary_rank = 2 selects employees at the second-highest distinct salary in their own department.The same techniques work for any numeric column. Consider a product table containing product IDs, product names, and prices.
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL(10, 2)
);SELECT DISTINCT price
FROM products
WHERE price IS NOT NULL
ORDER BY price DESC
LIMIT 1 OFFSET 1;SELECT DISTINCT price
FROM products
WHERE price IS NOT NULL
ORDER BY price DESC
LIMIT 1 OFFSET 2;SELECT product_id, product_name, price
FROM products
WHERE price = (
SELECT DISTINCT price
FROM products
WHERE price IS NOT NULL
ORDER BY price DESC
LIMIT 1 OFFSET 1
);This returns every product whose price equals the second-highest distinct price.
Ranking can also be applied to sales transactions or summarized sales totals. The important consideration is whether you are ranking individual transaction amounts or aggregated sales per employee, customer, or region.
SELECT DISTINCT transaction_amount
FROM sales
WHERE transaction_amount IS NOT NULL
ORDER BY transaction_amount DESC
LIMIT 1 OFFSET 2;This returns the third-highest distinct transaction amount.
WITH employee_sales AS (
SELECT
employee_id,
SUM(sales_amount) AS total_sales
FROM sales
GROUP BY employee_id
),
ranked_sales AS (
SELECT
employee_id,
total_sales,
DENSE_RANK() OVER (
ORDER BY total_sales DESC
) AS sales_rank
FROM employee_sales
)
SELECT employee_id, total_sales
FROM ranked_sales
WHERE sales_rank = 2;This query first calculates total sales for each employee and then ranks those totals. It finds employees tied at the second-highest total sales value.
Problem: The second row has the same value as the first row.
Cause: The query sorts individual records but does not remove duplicate values.
Solution: Use DISTINCT for distinct-value ranking or DENSE_RANK() for ranked records.
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1;Problem: The query does not return a result for the requested rank.
Possible causes:
Solution: Check the distinct values and confirm that N is valid.
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC;Problem: The result includes a numeric value but no employee details.
Cause: The query selects only the salary column or uses an aggregate function such as MAX().
Solution: Use a subquery to retrieve matching employee records or use DENSE_RANK() in a CTE.
Problem: MySQL reports an error when using DENSE_RANK() or ROW_NUMBER().
Cause: The server may be running a MySQL version earlier than 8.0.
Solution: Check the server version and use a compatible alternative such as MAX() with a subquery or DISTINCT with LIMIT.
SELECT VERSION();Problem: The returned value does not match the expected rank.
Cause: The offset may not equal N-1, or duplicate values may be included unintentionally.
Solution: Verify the ranking requirement, use DISTINCT when needed, and calculate the offset correctly.
Problem: Employees with equal salaries appear in a different order across executions.
Cause: The query sorts only by salary, leaving tied rows without a specified order.
Solution: Add a unique tie-breaker such as employee ID when using row-based ranking.
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC, employee_id ASC;If the table contains many records and salary ranking is a frequent operation, an index on the salary column may help the database access or order values more efficiently. Actual performance depends on the query plan, table size, data distribution, and other indexes.
CREATE INDEX idx_employees_salary
ON employees(salary);Indexes require storage and add overhead to inserts, updates, and deletes. Create them based on measured workload rather than automatically adding an index for every column.
Use DISTINCT with LIMIT when the goal is a single Nth-highest distinct value. Use DENSE_RANK() when the goal is to retrieve every record belonging to a distinct rank.
For a fixed second-highest value, a simple MAX() subquery is often sufficient. For a dynamic Nth-highest value, a DISTINCT and LIMIT query or a window function may be more readable.
Decide whether NULL values should be excluded or handled separately. For salary ranking, excluding NULL salaries is usually the clearest choice.
Before writing the query, decide whether you need:
For performance analysis, use EXPLAIN to inspect how MySQL plans to execute a query.
EXPLAIN
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1;Review the plan and test with realistic data before deciding whether an index or query rewrite is necessary.
Project Objective: Create a set of SQL queries that identifies the highest, second-highest, and Nth-highest distinct salaries in an employee database.
SELECT MAX(salary) AS highest_salary
FROM employees;SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1;SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 2;WITH ranked_employees AS (
SELECT
employee_id,
employee_name,
department,
salary,
DENSE_RANK() OVER (
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;After completing the project, you should be able to select the appropriate ranking technique, handle duplicate values correctly, retrieve complete employee records, and explain the difference between value ranking and row numbering.
MAX().LIMIT and OFFSET.DENSE_RANK() to find employees with the fourth-highest distinct salary.ROW_NUMBER() to retrieve the fifth row when employees are sorted by salary.Answer: It is commonly the second-largest distinct salary value. It can be found using a subquery with MAX(), or with DISTINCT, ORDER BY, and LIMIT.
SELECT MAX(salary)
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1;Answer: Both assign the same rank to tied values. RANK() leaves gaps after ties, while DENSE_RANK() continues with the next consecutive rank.
Answer: ROW_NUMBER() gives every row a unique sequential number. DENSE_RANK() assigns the same rank to rows with equal ordered values.
Answer: Use DISTINCT with descending order and an offset of N-1, or use DENSE_RANK() and filter by the desired rank.
Answer: It removes duplicate values so repeated salaries do not occupy multiple positions in a distinct-value ranking.
Answer: Find the second-highest distinct salary and filter employees whose salary equals that value, or use DENSE_RANK() and select rank 2.
Answer: If the table has fewer than two distinct non-NULL salary values, the second-highest value cannot be found. A direct result-set query returns no rows; a scalar subquery returns NULL.
Answer: Yes. MySQL 8.0 and later support window functions such as ROW_NUMBER(), RANK(), and DENSE_RANK().
Answer: Use DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) and filter for rank 2.
Answer: NULL represents missing or unknown data. Filtering NULL values makes the ranking rule clear and prevents missing salaries from being treated as valid salary values.
Answer: OFFSET specifies how many rows to skip before returning rows. For the Nth row in a sorted list, the offset is N-1.
Answer: For a single distinct value, DISTINCT with LIMIT and OFFSET is concise. For all records at a particular distinct rank, DENSE_RANK() is often more convenient.
SELECT VERSION();MAX(column_name).MAX() with a less-than subquery or DISTINCT with LIMIT 1 OFFSET 1.DISTINCT or DENSE_RANK() when ranking distinct values.WHERE column_name IS NOT NULL when missing values should be excluded.PARTITION BY department.DISTINCT when duplicate values must count only once.DENSE_RANK() when all records at a particular distinct rank are needed.ROW_NUMBER().EXPLAIN to inspect query performance when necessary.Finding the second-highest and Nth-highest values in MySQL is an essential SQL skill used in database development, reporting, data analysis, and technical interviews. The best solution depends on the ranking requirement. MAX() with a subquery is a straightforward approach for the second-highest distinct value. DISTINCT with ORDER BY, LIMIT, and OFFSET provides a concise method for any fixed Nth-highest distinct value. Window functions such as DENSE_RANK(), RANK(), and ROW_NUMBER() are useful when ranking complete records or handling ties.
By practicing these methods with duplicate values, NULL values, department-wise ranking, and missing ranks, you can write more accurate SQL queries and confidently solve common MySQL interview problems.
Continue Learning: Explore the MySQL Training Course to strengthen your database skills, and register for a course demo to learn more.
