Ad:

MySQL Discussion Thread
Marked Solved
Views: 2071

Aug 25th, 2008

What to fetch second highest marks?

Expand Post »

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

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

22 posts
since Aug 2008

Permalink

Aug 27th, 2008

Re: What to fetch second highest marks?

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

dharam_05

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

22 posts
since Aug 2008

Permalink

Aug 27th, 2008

Re: What to fetch second highest marks?

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

MySQL Syntax (Toggle Plain Text)
 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;
 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;

varmadba

Reputation Points: 22

Solved Threads: 9

Junior Poster in Training

Offline

83 posts
since Jun 2008

Permalink

Aug 28th, 2008

Re: What to fetch second highest marks?

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

dharam_05

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

22 posts
since Aug 2008

Permalink

Aug 28th, 2008

Re: What to fetch second highest marks?

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...

dharam_05

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

22 posts
since Aug 2008

Permalink

Sep 5th, 2008

Re: What to fetch second highest marks?

This is how i goes

MySQL Syntax (Toggle Plain Text)
 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;
 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 :

MySQL Syntax (Toggle Plain Text)
 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
 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

varmadba

Reputation Points: 22

Solved Threads: 9

Junior Poster in Training

Offline

83 posts
since Jun 2008

Permalink

Sep 5th, 2008

Re: What to fetch second highest marks?

Thank you!!

dharam_05

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

22 posts
since Aug 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: pass php variables to mysql database

Next Thread in MySQL Forum Timeline: MySQL Quick Reference Card

DaniWeb Message

Cancel Changes

User Name
Password