January 17, 2013 at 11:31 pm
I have 2 tables
CREATE TABLE tbl_Student
(
Student_ID INT ,
Name VARCHAR(10)
);
GO
CREATE TABLE tbl_Marks
(
Student_ID INT ,
Subject VARCHAR(10),
Marks INT
);
GO
INSERT INTO tbl_Student(Student_ID, Name)
VALUES (1, 'Aakash'),(2, 'Arun'),(3, 'Seema');
GO
INSERT INTO tbl_Marks(Student_ID, Subject, Marks)
VALUES (1, 'computer',80),(1, 'english',70);
GO
INSERT INTO tbl_Marks(Student_ID, Subject, Marks)
VALUES (2, 'computer',50),(2, 'english',40);
GO
INSERT INTO tbl_Marks(Student_ID, Subject, Marks)
VALUES (3, 'computer',90),(3, 'english',95);
GO
What is the query to calculate the student who obtained highest total marks.
O/P should be
3 Seema 185
January 17, 2013 at 11:48 pm
What have you tried so far ?
January 18, 2013 at 12:20 am
the below query works and i get the desired o/p but i dont think it is an efficient way.
select s.StudentID, s.name, sum(marks)
from tbl_marks m inner join tbl_student s
on m.studentid=s.studentid
group by s.studentid
having sum(marks)=
(
select max(tbl.totalmarks) from
(select studentid, sum(marks) as totalmarks from tbl_marks group by studentid) tbl
)
is there a better efficient query that doesnt require a lot of nested queries as above, please?
January 18, 2013 at 12:27 am
the below query is another way which gives the desired output but this is also a workaround.
select top 1 s.studentid, s.name, sum(marks)
from tbl_marks m inner join tbl_student s
on m.studentid=s.studentid
group by s.studentid
order by sum(marks) desc
is there a better efficient way to get the o/p please?
January 18, 2013 at 1:03 am
Check the execution plans of the queries, each one will be different in some way
There are many different ways to get to the output.
You could look at ranking functions like ROW_NUMBER, RANK, DENSE_RANK wrapped in a CTE (common table expression) then filter out the one(s) you want
January 18, 2013 at 1:47 am
Keep in mind that the 2 solutions you have given will only really be valid if all students have the same number of courses.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply