Comprehensive Analysis and Implementation of Querying Maximum and Second Maximum Salaries in MySQL

Introduction

In the fields of database management and data analysis, querying salary rankings is a common and important task. Particularly in scenarios such as human resource management, compensation analysis, and business decision support, quickly and accurately obtaining the highest and second-highest salary information holds significant practical value. Based on common requirements in actual development, this article systematically explores multiple technical approaches to achieve this functionality in MySQL environments.

Problem Definition and Data Model

Assume we have a simple employee table named Employee, containing two core fields: Employee ID and Salary. Sample data is shown below:

Employee
Employee ID    Salary
  3            200
  4            800
  7            450

Our objective is to write an SQL query that can simultaneously return both the maximum salary and the second maximum salary values, with the expected output format being:

max_salary   2nd_max_salary
  800             450

Core Solution Analysis

Combined Query Approach Using Subqueries

The most direct and effective method involves using two independent subqueries to calculate the highest and second-highest salaries separately, then combining the results into a single row output. The advantage of this approach lies in its clear logic, ease of understanding, and good handling of various edge cases.

Implementation code:

SELECT
  (SELECT MAX(Salary) FROM Employee) AS max_salary,
  (SELECT MAX(Salary) FROM Employee
   WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee)) AS 2nd_max_salary

The working principle of this query can be broken down into three steps:

1. The first subquery (SELECT MAX(Salary) FROM Employee) directly calculates the maximum salary in the table
2. The second subquery obtains the second-highest salary by excluding the maximum salary and then finding the maximum again
3. The two results are combined into the final output through the SELECT statement's column definitions

Performance Characteristics and Optimization Considerations

Although this solution is logically intuitive, it may encounter performance bottlenecks in large-scale data scenarios. Each subquery requires a full table scan, theoretically resulting in O(n) time complexity. For small to medium-sized datasets, this overhead is generally acceptable; however, for extremely large data tables, it is recommended to consider establishing appropriate indexes to optimize query performance.

Alternative Approach Comparison

Method Using LIMIT Clause

MySQL's unique LIMIT syntax provides another concise implementation method:

SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 2

This method first sorts salaries in descending order, then uses LIMIT 2 to obtain the top two results. If conversion to horizontal format is required, further processing can be applied:

SELECT
  MAX(CASE WHEN rn = 1 THEN Salary END) AS max_salary,
  MAX(CASE WHEN rn = 2 THEN Salary END) AS 2nd_max_salary
FROM (
  SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn
  FROM (SELECT DISTINCT Salary FROM Employee) t
) ranked

General Ranking Function Solution

For database systems supporting window functions (such as MySQL 8.0+), a more modern approach can be used:

WITH RankedSalaries AS (
  SELECT Salary,
         DENSE_RANK() OVER (ORDER BY Salary DESC) AS salary_rank
  FROM Employee
)
SELECT
  MAX(CASE WHEN salary_rank = 1 THEN Salary END) AS max_salary,
  MAX(CASE WHEN salary_rank = 2 THEN Salary END) AS 2nd_max_salary
FROM RankedSalaries

The advantage of this approach lies in its natural handling of identical salaries and easy extension to query salaries of any rank.

Edge Case Handling

Insufficient Data Volume Situations

When the table contains fewer than 2 records, the second-highest salary query may return NULL values. In practical applications, this edge case should be considered and can be handled using COALESCE function or other logic:

SELECT
  (SELECT MAX(Salary) FROM Employee) AS max_salary,
  COALESCE(
    (SELECT MAX(Salary) FROM Employee
     WHERE Salary < (SELECT MAX(Salary) FROM Employee)),
    NULL
  ) AS 2nd_max_salary

Duplicate Salary Handling

When multiple employees share the same salary, business logic needs to be clearly defined. If strict ranking distinction is required, DISTINCT or DENSE_RANK() should be used; if tied rankings are permitted, raw data can be used.

Performance Optimization Recommendations

Indexing Strategy

To improve query performance, it is recommended to create an index on the Salary field:

CREATE INDEX idx_salary ON Employee(Salary DESC)

Descending indexes are particularly suitable for query scenarios requiring maximum value retrieval, significantly reducing the overhead of sorting operations.

Query Execution Plan Analysis

Use the EXPLAIN statement to analyze query execution plans, ensuring queries can effectively utilize indexes:

EXPLAIN SELECT
  (SELECT MAX(Salary) FROM Employee) AS max_salary,
  (SELECT MAX(Salary) FROM Employee
   WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee)) AS 2nd_max_salary

Practical Application Scenarios

Compensation Analysis Reports

When generating compensation analysis reports, comparing the highest and second-highest salaries can help enterprises understand the rationality of their compensation structure and identify potential salary anomalies.

Performance Evaluation Support

In performance evaluation systems, salary ranking information can serve as an important reference indicator for assessing employee value and contribution levels.

Budget Planning Reference

When financial departments conduct annual budget planning, understanding the internal salary distribution within the company is essential, with maximum salary information being particularly important for developing reasonable compensation budgets.

Extended Application: Querying Nth Highest Salary

Based on the same technical principles, we can extend the query logic to retrieve salaries of any rank:

-- General method for querying Nth highest salary
SELECT Salary
FROM Employee Emp1
WHERE (N-1) = (
  SELECT COUNT(DISTINCT Emp2.Salary)
  FROM Employee Emp2
  WHERE Emp2.Salary > Emp1.Salary
)

Or using LIMIT syntax:

SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT N-1, 1

Conclusion and Best Practices

This article systematically introduces multiple technical solutions for querying the highest and second-highest salaries in MySQL. The combined query approach based on subqueries, with its clear logic and good compatibility, serves as the preferred solution, particularly suitable for most business scenarios. For specific requirements, such as handling duplicate values or pursuing optimal performance, consideration can be given to using window functions or optimized indexing strategies.

In actual project development, it is recommended to select appropriate implementation methods based on specific data scale, performance requirements, and business logic. Meanwhile, good error handling and edge case consideration are important guarantees for ensuring system stability. By mastering these core technologies, developers can more efficiently solve similar ranking query problems and provide accurate data support for business decisions.