Option for row_number and partition by in SQL Server 2000? -

here table student_grade:

id  studentname subcode,grade stdid ----------------------------------- 1   pinky         1       1 2   archana       2       1 3   leena         1       1 4   leena         2       1 5   leena         3     b   1 6   rupali        1       2 7   megha         1       2 8   smita         1       1 

grade d.i want show record once when grade same same stid,does not matter subcode , studentname.for perticular subcode,appropriate studentname come when stid , grade specidy.if grade different same stid,show both record. here want show

i applied row_numer()

working in sql server 2005 not working in sql server 2000. how in 2000?

query is:

select      stdid, subcode, studentname, grade, subjectname        (select          stdid, subcode, studentname, grade,          subjects.subjectname subjectname,         row_number() over(partition cast(stdid varchar) +' - '+ grade order subcode desc,studentname desc) rn                 student_grade      inner join          subjects on subjects.id = student_grade.subcode) t       rn = 1 

result :

stdid subcode studentname grade subjectname 1       2     leena           marathi 1       3    leena         b    hindi 2      1     rupali           english