vendredi 5 juin 2015

Second maximum value by using dense rank function

i have table like

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

in that table i assumee the rank of the table is '

name   marks rank 
raja    88    1
ravi    88    2
karthik  99   3
praveen  99   4
vijay    70   5

iget that by using dense rank function

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

but what i need is from that bove thabe i need to get second lowest rank i mena the output i want is "praveen 99 4"

i tried to get thesecond lowest rank but i cant get the all the columns ,i tried thse query

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 )

Aucun commentaire:

Enregistrer un commentaire