Group datasets and sort groups internally

Suppose there is a table as follows (for example only ):

Create Table DBO. Score
(
Scoreid int identity (1, 1)
, Student nvarchar (max)
, Subjectid nvarchar (max)
, Scorenumber int
)

Insert into score values ('A', 'mat', 96)
Insert into score values ('A', 'China', 90)
Insert into score values ('A ','ProgramDesign ', 77)
Insert into score values ('B', 'mat', 76)
Insert into score values ('B', 'China', 66)
Insert into score values ('B', 'program Design', 88)
Insert into score values ('C', 'mat', 60)
Insert into score values ('C', 'China', 80)
Insert into score values ('C', 'Programming ', 66)

Sort scores of each subject, for example:

Available new functions in SQL Server 2005:

 
Select student, subjectid, scorenumber, row_number () over (partition by subjectid order by scorenumber DESC) as [Rank]
 

 
From score
 

The where statement can be used to filter out the first few students in each subject.