如何修改SQL语句获取Students表中第二高分的完整记录

Got it, let's fix this issue! The problem with your current query SELECT * FROM Students WHERE Score < (SELECT MAX(Score) FROM Students); is that it pulls every record with a score lower than the absolute highest—not just the ones that have the second-highest score. Here are a few reliable ways to get exactly the full records for the second-highest score:

Method 1: Subquery to Target the Second-Highest Score Directly #

This approach builds on the logic you already used to get the second-highest value, but uses that value to filter the full records:

SELECT * 
FROM Students 
WHERE Score = (
    -- First get the max score that's lower than the overall highest
    SELECT MAX(Score) 
    FROM Students 
    WHERE Score < (SELECT MAX(Score) FROM Students)
);

This works because the inner subquery finds the highest score that isn't the top one (your original second-highest value), then we match all records with that score. Perfect if multiple students share the second-highest score—they'll all show up.

Method 2: Window Functions (Modern Databases) #

If your database supports window functions (like MySQL 8+, PostgreSQL, SQL Server, etc.), this is a cleaner, more flexible approach, especially if you might need to handle rankings later:

SELECT *
FROM (
    SELECT *,
           -- Assign ranks where same scores get the same rank
           DENSE_RANK() OVER (ORDER BY Score DESC) AS score_rank
    FROM Students
) ranked_students
WHERE score_rank = 2;

Using DENSE_RANK() ensures that if multiple students have the same second-highest score, they all get a rank of 2. If you used RANK() instead, it would skip ranks for tied values (e.g., two students with the highest score would make the next rank 3), so DENSE_RANK() is usually the right fit here.

Method 3: LIMIT + OFFSET (Simple but Limited) #

For databases like MySQL that support LIMIT, you can sort the records and skip the top one—but note this only returns one record, even if multiple students have the second-highest score:

SELECT *
FROM Students
ORDER BY Score DESC
LIMIT 1 OFFSET 1;

If you want all tied second-highest records with this method, you'd need to first get the second-highest score value and filter, which brings you back to Method 1.

Quick Note #

If your table might have duplicate scores for the second-highest rank, go with Method 1 or 2—they'll return all matching records. Method 3 is only good if you're sure there's exactly one second-highest record.

内容的提问来源于stack exchange，提问作者Archita Sundaray