SQL - 2nd highest record in table

问题:

SELECT MAX(Score)
FROM Students
WHERE Score < (SELECT MAX(Score) FROM Students);

the above query works perfectly and fetches the record that has 2nd highest score, whereas the query mentioned below does not fetch anything

SELECT *
FROM Students
WHERE Score < (SELECT MAX(Score) FROM Students);

here Students is the table from which I want to fetch all the details of that record which has 2nd highest score in the entire table.

I want that 2nd query should get executed, thanks in advance for helping me out.

• I have not used any database, I'm simply trying out these queries in w3schools.

回答1:

With standard SQL, this is typically solved using window functions:

select *
from (
  select *, dense_rank() over (order by score desc) as rnk
  from students
) t
where rnk = 2;

The above is ANSI standard SQL and works on all modern DBMS.

回答2:

How about ordering and getting the first record returned using LIMIT:

SELECT * FROM Students WHERE Score < (SELECT MAX(Score) FROM Students) ORDER BY Score DESC LIMIT 1;