如何使用SQL获取第二最大值？面试问题及解法求点评

Hey there! Let’s start by unpacking what your query is trying to do—it looks like you’re aiming to find the second highest ID in your table, right? Let’s break down your solution and talk about some edge cases, improvements, and alternative approaches.

First, let’s look at your current approach:

Select max(ID) from table where ID not in (select max(ID) from table)

This works for most standard scenarios: it first grabs the largest ID via the subquery, filters out that value from the table, then takes the max of the remaining IDs to get the second highest. That’s a clever, straightforward way to tackle the problem without relying on more advanced syntax—great thinking!

But there are a few things to keep in mind:

• Duplicate top IDs: If multiple rows have the maximum ID (e.g., IDs are 5, 5, 3), your query still works perfectly—it excludes all 5s and returns 3 as expected.
• All IDs are identical: If every row has the same ID, your query will return NULL because there are no IDs left after filtering out the max. Depending on your use case, you might want to handle this (like returning a default value with COALESCE).
• Performance for large tables: This query scans the table twice—once for the subquery, once for the main query. For very large datasets, this isn’t the most efficient approach compared to methods that scan the table only once.

Alternative Approaches #

Here are some cleaner or more efficient ways to achieve the same goal, depending on your database’s capabilities:

1. Using ORDER BY + LIMIT/OFFSET (simple & widely supported)

If your database supports LIMIT (MySQL, PostgreSQL) or similar syntax, this is the most concise option:

-- Get the second highest ID (including duplicates of the second highest)
SELECT ID FROM table ORDER BY ID DESC LIMIT 1 OFFSET 1;

-- If you want the second highest *distinct* ID
SELECT DISTINCT ID FROM table ORDER BY ID DESC LIMIT 1 OFFSET 1;

Note: If there’s no second highest ID (all IDs are the same), this returns an empty result set instead of NULL—adjust based on your needs.

2. Using Window Functions (flexible for rankings)

For databases that support window functions (MySQL 8+, PostgreSQL, SQL Server, etc.), DENSE_RANK() is ideal for handling duplicate values gracefully:

SELECT DISTINCT ID
FROM (
    SELECT 
        ID,
        DENSE_RANK() OVER(ORDER BY ID DESC) AS rank_num
    FROM table
) ranked
WHERE rank_num = 2;

This returns all distinct IDs that are the second highest, even if multiple rows have that ID. It’s also more efficient for large tables since it scans the data only once.

3. Handling the "no second highest" case

If you want to return a default value instead of NULL when there’s no second highest ID, tweak your original query with COALESCE:

SELECT COALESCE(
    (Select max(ID) from table where ID not in (select max(ID) from table)),
    0 -- Replace with your desired default value
) AS second_highest_id;

Overall, your original solution is totally valid for basic use cases, especially if you’re working with older databases that don’t support newer syntax. The alternatives just offer more flexibility and better performance in most scenarios!

内容的提问来源于stack exchange，提问作者Elié Abisaí Díaz Vásquez