MySQL

What to fetch second highest marks?

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Aug 2008

Posts: 22

Reputation: dharam_05 is an unknown quantity at this point

Solved Threads: 0

dharam_05

Offline

Newbie Poster

What to fetch second highest marks?

Aug 25th, 2008

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.

•

Join Date: Aug 2008

Posts: 22

Reputation: dharam_05 is an unknown quantity at this point

Solved Threads: 0

dharam_05

Offline

Newbie Poster

Re: What to fetch second highest marks?

Aug 27th, 2008

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

•

Join Date: Jun 2008

Posts: 79

Reputation: varmadba is an unknown quantity at this point

Solved Threads: 8

varmadba

Offline

Junior Poster in Training

Re: What to fetch second highest marks?

Aug 27th, 2008

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

 Help with Code Tags 
 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;

:- Varma

We are Happy to inform launch of a new site with loads of database related information Site offers wide range of functionality Forums,Blogs,Articles,Editorials and much more
http://www.sqllibrarian.info/

•

Join Date: Aug 2008

Posts: 22

Reputation: dharam_05 is an unknown quantity at this point

Solved Threads: 0

dharam_05

Offline

Newbie Poster

Re: What to fetch second highest marks?

Aug 28th, 2008

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

•

Join Date: Aug 2008

Posts: 22

Reputation: dharam_05 is an unknown quantity at this point

Solved Threads: 0

dharam_05

Offline

Newbie Poster

Re: What to fetch second highest marks?

Aug 28th, 2008

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

•

Join Date: Jun 2008

Posts: 79

Reputation: varmadba is an unknown quantity at this point

Solved Threads: 8

varmadba

Offline

Junior Poster in Training

Re: What to fetch second highest marks?

Sep 5th, 2008

This is how i goes

 Help with Code Tags 
 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 :

 Help with Code Tags 
 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