如何在存在重复薪资的employee表中获取第二高薪资的员工记录？

To retrieve all employees who earn the second highest salary from your employee table, here are two reliable approaches that handle ties correctly (since multiple employees can share the same second highest salary):

Method 1: Using a Subquery with DISTINCT and LIMIT/OFFSET #

This method first identifies the second highest distinct salary, then fetches all employees matching that salary.

SELECT emp_id, emp_name, salary
FROM employee
WHERE salary = (
    -- Get the second highest distinct salary
    SELECT DISTINCT salary
    FROM employee
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1
);

How it works: #

1. The subquery uses DISTINCT to get unique salary values, sorts them in descending order (ORDER BY salary DESC), then skips the first (highest) salary with OFFSET 1 and selects the next one with LIMIT 1.
2. The main query selects all employees whose salary matches the value returned by the subquery.

Notes: #

• Works in most SQL databases (MySQL, PostgreSQL, SQLite, etc.).
• If there’s no second highest salary (e.g., all employees have the same salary), the subquery returns NULL, so the main query will return no results (which is logically correct).

Method 2: Using Window Functions (DENSE_RANK) #

This approach uses the DENSE_RANK() window function to assign a rank to each salary (without skipping ranks for ties), then filters for employees with rank 2.

WITH ranked_employees AS (
    SELECT 
        emp_id, 
        emp_name, 
        salary,
        -- Assign rank based on salary (descending), same salaries get same rank
        DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employee
)
SELECT emp_id, emp_name, salary
FROM ranked_employees
WHERE salary_rank = 2;

How it works: #

1. The CTE (ranked_employees) adds a salary_rank column where the highest salary gets rank 1, the next distinct salary gets rank 2, and so on. Unlike RANK(), DENSE_RANK() doesn’t skip ranks if there are ties (e.g., all employees with 6000 get rank 2 in your sample data).
2. We then select all rows where salary_rank equals 2.

Notes: #

• Requires support for window functions (available in MySQL 8+, PostgreSQL, SQL Server, Oracle, etc.).
• This method is more flexible if you later need to fetch the 3rd, 4th, etc., highest salaries—just change the salary_rank value.

Edge Cases to Consider #

• No second highest salary: If all employees have the same salary, both queries return no results (since there’s no "second" salary to match).
• Multiple ties at the highest salary: For example, if two employees earn 8000, DENSE_RANK() still assigns rank 1 to both, and rank 2 to the next distinct salary (6000), which is correct.

内容的提问来源于stack exchange，提问作者Wakar Ahamad