Question 230592 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – what sql query should i write to find rollno of student whose average marks are maximum where marks and student are different tables?

this is my student table stud table

rollno name
1 A
2 B
3 C
4 B
5 D
6 C

this is my marks table marks table

rollno mar eng maths phy chem
1 40 45 38 50 50
2 28 50 45 41 38
3 41 42 43 44 45
4 45 44 43 42 41
5 33 32 42 15 41

for following query:

select rollno,(mar+eng+maths+phy+chem)/5 as average from marks;

I get o/p: op

rollno average
1 44.6000
2 40.4000
3 43.0000
4 43.0000
5 32.6000

now to find rollno of student whose average is maximum what query should I write?

I tried using

select rollno,max(
                    select (mar+eng+maths+phy+chem)/5 from marks
                    ) 
from marks;

which gives me syntax error

Tags:

2

Answers


  1. 0

    The syntax on this is incorrect as the subquery returns multiple rows. Only one value should be used in the max function.

    This is a better way of doing it –

    SELECT rollno FROM marks WHERE (mar+eng+maths+phy+chem)/5 = (SELECT MAX((mar+eng+maths+phy+chem)/5) FROM marks);
    Login or Signup to reply.
  2. 0

    Another option would be to use limit 1;

        select rollno,(mar+eng+maths+phy+chem)/5 as average from marks
    order by (mar+eng+maths+phy+chem) desc
    limit 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search