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'
Santhosh Kumar JayaramanPosted Jun 26, 2012, 1:27 AM
create table test
(studentId int,
TestSubject varchar(50),
Marks int
Primary key (studentId, TestSubject)
)
insert into test values(1,'English',70)
insert into test values(2,'English',80)
insert into test values(3,'English',67)
insert into test values(4,'English',89)
insert into test values(1,'Science',87)
insert into test values(2,'Science',86)
insert into test values(3,'Science',68)
insert into test values(4,'Science',82)
select * from test where Marks in (Select MAX(marks) from test group by TestSubject)
The above query will fetch the max marks and student Id for subject english and science