Thursday, March 17, 2011

Retrieving the first two scores of a student

create table marks(
StudentID int not null
,StudentName varchar(10) not null
,Subject varchar(10) not null
,Score int not null)

insert into marks values(1,'sanjeev','Math',87)
insert into marks values(1,'sanjeev','Geography',76)
insert into marks values(1,'sanjeev','History',98)
insert into marks values(1,'sanjeev','Science',85)
insert into marks values(2,'gourav','Crafts',89)
insert into marks values(2,'gourav','Science',88)
insert into marks values(2,'gourav','History',76)
insert into marks values(3,'Munish','English',87)
insert into marks values(3,'Munish','Science',76)
insert into marks values(3,'Munish','Geography',83)

In 2000
select studentname, subject, score
from marks o
where score in (select top 2 score from marks i where i.studentname = o.studentname order by score desc)
order by studentname,score desc

In 2005

select studentname, subject, score
select studentname, subject, score,
row_number() over (partition by studentid order by score desc) as rownum
from marks
) dt
where rownum<=2

No comments:

Post a Comment