sql query to find topper in a class

[jhansi]

hi...
i need the solution 4r this, in code some mistake is there, it has to print the topper of the class and his name. and also it should print two persons, if two of them got top marks. But i am not getting it
if any one can do this kindly help me.........

this is my code:

select max(c.m) max
from (select avg(marks)as m,stdid
from student2
 group by stdid
)c
inner join
(select avg(marks)as m,stdid
from student2
group by stdid)d
on c.stdid= d.stdid;

[amitpatel66]

hi...
i need the solution 4r this, in code some mistake is there, it has to print the topper of the class and his name. and also it should print two persons, if two of them got top marks. But i am not getting it
if any one can do this kindly help me.........

this is my code:

select max(c.m) max
from (select avg(marks)as m,stdid
from student2
group by stdid
)c
inner join
(select avg(marks)as m,stdid
from student2
group by stdid)d
on c.stdid= d.stdid;

Try this:

 
SELECT stdid, mrk FROM
(SELECT stdid, mrk, RANK() OVER(ORDER BY mrk desc) rnk FROM (SELECT stdid,AVG(marks) as mrk FROM student2 GROUP BY stdid ORDER BY mrk desc)) WHERE rnk = 2
 
 

[mwasif]

Hi jhansi,

Welcome to TSDN!!!

Kindly use proper tags when posting code.

[jhansi]

Try this:

 
SELECT stdid, mrk FROM
(SELECT stdid, mrk, RANK() OVER(ORDER BY mrk desc) rnk FROM (SELECT stdid,AVG(marks) as mrk FROM student2 GROUP BY stdid ORDER BY mrk desc)) WHERE rnk = 2
 
 

thank u for ur response.........


jhansi.

[amitpatel66]

thank u for ur response.........


jhansi.

You are welcome :)

MODERATOR