Fetching extra attribute in group by clause MySQL

Question

I have the following SQL relational schema

create table students(
  s_id int primary key auto_increment,
  s_name char(30),
  s_city char(30),
  s_number char(10)
)

create table subjects(
  sub_id int primary key auto_increment,
  sub_name char(30)
);

create table marks(
  m_id int primary key auto_increment,
  s_id int,
  sub_id int,
  marks int CHECK (marks <= 100 AND marks >= 0)
);

The database stores the information of student along with there marks in each subject.

I am able to fetch the maximum marks obtained in each subject using the following SQL query.

select sub_name, max(marks) as highest from subjects as sub, marks as m where sub.sub_id = m.sub_id group by sub_name;

and it gave me the following result:

+-----------+---------+
| sub_name  | highest |
+-----------+---------+
| Chemistry |     100 |
| Maths     |      98 |
| Physics   |      90 |
+-----------+---------+

Now I want to add an extra attribute to the above result which is s_name which will specify the name of the student who obtained those marks, for that I used the following query.

select sub_name as subject, s_name as student, min(marks) as lowest from subjects as sub, marks as m, students as s where s.s_id = m.s_id and sub.sub_id = m.sub_id group by sub_name;

and it gave me the following result.

+-----------+-------------+--------+
| subject   | student     | lowest |
+-----------+-------------+--------+
| Chemistry | Mohd Shibli |     86 |
| Maths     | Mohd Shibli |     96 |
| Physics   | Mohd Shibli |     79 |
+-----------+-------------+--------+

The result obtained is not correct as in the 'student' column it is fetching the name of the first student-only instead of fetching the correct name.

Can anyone help me out for writing the query which can give the results about the max marks obtained in each subject and which student obtained it.

Follow the tag I added; lots of examples of how to do such. – Rick James Dec 07 '18 at 04:59 — Rick James, Dec 07 '18 at 04:59

score 0 · Answer 1 · answered Dec 07 '18 at 05:07

You can try below using correlated subquery

select sub_name as subject, s_name as student,m.marks
from marks m 
inner join subjects as sub on m.sub_id=sub.sub_id
inner join students as s m.s_id=s.s_id
WHERE m.marks =
   (SELECT MAX(mm.marks) FROM marks mm GROUP BY 
         mm.sub_id HAVING mm.sub_id=m.sub_id)

Fetching extra attribute in group by clause MySQL

1 Answers1