Member Avatar for 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.

  • 2 Contributors
  • 6 Replies
  • 361 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by dharam_05

Recommended Answers

All 6 Replies

Member Avatar for dharam_05

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

Member Avatar for 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;
Member Avatar for dharam_05

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

Member Avatar for 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...

Member Avatar for 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

commented: Nice work +10
Member Avatar for dharam_05

Thank you!! :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.