CODEWITHSUNDEEP
mysqlsqldatabase
NJF
NJF

Reputation: 435

select a student with max marks from student and marks table

I have two tables student and mark. students table has stid and name where marks table has stid and marks. I want to find a student with highest marks. How to do it?

Upvotes: 0

Views: 3219

Answers (2)

MikeMatusiak
MikeMatusiak

Reputation: 403

I assume you want the max of average of student marks (as you wrote "student with highest marks" - so I assume one student has many marks.

Here the example schema, data and query:

create table student (
    stid int,
    name varchar(100)
)

create table mark (
    stid int,
    mark int
);

insert into student values(1, 'a');
insert into student values(2, 'b');
insert into student values(3, 'c');

insert into mark values(1, 5);
insert into mark values(1, 4);
insert into mark values(2, 6);
insert into mark values(2, 5);
insert into mark values(3, 4);
insert into mark values(3, 4);

select s.stid, s.name, a.avg_mark
from (select stid, AVG(mark) as avg_mark from mark group by stid) as a, student s
Where a.avg_mark =
    (select Max(avg_mark) as max_mark from
        (select  stid, AVG(mark) as avg_mark from mark group by stid) as x)
and
s.stid = a.stid;

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

What I got from your question, you need the student with the highest mark

Try this:

Select st.stid ,  st.name, m.marks
from student st
inner join mark m on st.stid = m.stid
order by m.marks desc limit 1

Upvotes: 2

Related Questions