select a.*
from tablename a join
( select subject, max(score) as m
from tablename
group by subject
) b
on a.subject=b.subject and a.score=b.m
from tablename a join
( select subject, max(score) as m
from tablename
group by subject
) b
on a.subject=b.subject and a.score=b.m
create table #t
([name] varchar(20),subject varchar(20),score int)insert into #t
select aa.* from aa inner join
(select subject,score=max(score)
from aa
group by subject) bb on aa.subject=bb.subject and aa.score=bb.scoreselect * from aa inner join (
select subject,score from #t group by subject,score having count(subject)<2
) cc on aa.subject=cc.subject and aa.score=cc.scoredrop table #t
declare mycur cursor for
select subject,max(score) from #tt group by subject
open mycur
fetch next from mycur into @aa,@bb
while @@fetch_status=0
begin
select * from #tt where subject=@aa and score=@bb
fetch next from mycur into @aa,@bb
end