What to fetch second highest marks?

[dharam_05]

Hi i want need to fetch sec highest marks from given tables.
my database design is like:
My database design:
Table 1:

Student table
(PK)Stud_id, Stud_name

Table 2:

Subject table
(pk)Sub_id, Sub_name, Marks.

Table 3:

Stud_Sub table
Stud_id, Sub_id

And i come up with the query:
Select Student.stud_id, Student.stud_name Max( subject.marks)from Student, subject, stud_sub
where marks < ( Select Max(sum(marks)) from student, subject, stud_sub where student.stud_id = stud_sub.stud_id AND stud_sub.sub_id = Subject.sub_id group by stud_id;

Can any one correct me, if this query is wrong coz m new in query writing.
please help me in finding the second highest marks in database.

[dharam_05]

no one can help me out...in this forum

[varmadba]

Hey
in the query you r using
"where marks is less than max(sum(marks))"
This will be true for all the records as individual marks will be less than sum of them

Select 
Student.stud_id, 
Student.stud_name,
Max(subject.marks)
from Student, subject, stud_sub
where sum(marks)< 
( Select Max(sum(marks)) from student, subject, stud_sub 
  where student.stud_id = stud_sub.stud_id 
  AND stud_sub.sub_id = Subject.sub_id group by stud_id)
group by stud_id
order by sum(marks) desc
limit 1;

[dharam_05]

Thanks for your reply...
i guess it going to work...

[dharam_05]

Can we put only subject.marks in place sum(marks)...
And one more thing can you please ellaborate how this query going to work, i mean in sub query it fetch one record or it going to fetch no. of records. just want to know how this query proceed...

[varmadba]

This is how i goes

Select 
Student.stud_id, 
Student.stud_name,
Max(subject.marks)
from Student, subject, stud_sub
where sum(marks)< 
( Select Max(sum(marks)) from student, subject, stud_sub 
  where student.stud_id = stud_sub.stud_id 
  AND stud_sub.sub_id = Subject.sub_id group by stud_id)
group by stud_id
order by sum(marks) desc
limit 1;

The Inner Query :

Select Max(sum(marks)) from student, subject, stud_sub 
  where student.stud_id = stud_sub.stud_id 
  AND stud_sub.sub_id = Subject.sub_id group by stud_id

will get the max of sum(marks) "only one value"

so the outer query will bring records which are less than value returned by the inner query
and as we are ordering by sum(marks) desc and limit 1
it will give you the second highest record

[dharam_05]

Thank you!! :)