sql - 使用密集秩函数的第二最大值

Question

我有这样的表:

name     marks
raja     88
ravi     88
karthik  99
praveen  99
vijay    70

在该表中，我假设表的排名是:

name      marks        rank 
karthirk    99           1
praveen     99           2
raja        88           3
ravi        88           4
vijay       77           5

我通过使用 dense rank() 函数得到它:

select  name,marks,dense_rank() over(  order by name ) as ranks
from std_D_D order by marks desc

但我需要的是从上面的表格中我需要获得倒数第二的排名。我的意思是我想要的输出是:

ravi 88   4

我试图获得第二低的排名，但我无法获得所有列。我试过这个查询:

select max(a.ranks) as b from (
select  name,marks,dense_rank() over(  order by name ) as ranks
from std_D_D ) as a where a.ranks not in
(

select max(a.ranks) as b from (
select  name,marks,dense_rank() over(  order by name ) as ranks
from std_D_D ) as a )

Answer 1

您可以颠倒排名顺序并选择第二个:

SELECT name, marks
FROM   (SELECT name, makrs, DENSE_RANK() OVER (ORDER BY name DESC) AS ranks
        FROM   std_D_D) t
WHERE  ranks = 2