select with dynamic rows

Question

i have a table in sql server .it have 5 columns testname, testid, testmarks, test%age. the task is to select rows with highest marks. there are 3 differnt subject with 2 rows of each. we have to select 3 rows of 3 subjects having highest marks.

in db i added 2 rows of word test 2 of excel 2 of internet means 6 rows in total. what we want is to fetch rows with highest mark mean we have to get 3 rows with 1 row of each subject.  records can b more than 1000  so i want to creat a dynamic query to sort this problm.


we use name as search criteria in where clause
e.g SELECT * FROM RESULT WHERE testname='some value'