CODEWITHSUNDEEP
mysqlsql
user3686918
user3686918

Reputation: 1

Can MySQL return the columns from a "max" row?

I have the following data in a table :-

Student     Subject     Marks
Peter       English     85
Peter       Maths       79
Peter       Science     80
Milind      English     71
Milind      Maths       95
Milind      Science     89

I want to write a query which will give me for each student, the subject in which s/he scored max. In this case, it will be 

Peter   English
Milind  Maths

Thanks in advance for the help. Milind.

Upvotes: 0

Views: 74

Answers (5)

user3619834
user3619834

Reputation: 1

Try this, this should work : Please revert in case of any Clarifications.

"StudentMarks is the name of the table"

;With MaxMarks as ( Select Name, Subject_, Marks as Highmarks from StudentMarks Group by Name, Subject_, Marks )

Select * from MaxMarks where Highmarks in (Select Max(Highmarks) from MaxMarks Group by Name )

Upvotes: 0

Hieu Vo
Hieu Vo

Reputation: 3284

there are few ways to do, you can try this solution

select
    t1.*
from 
    your_table t1 
    left join your_table t2 on t1.Student = t2.Student and t1.Marks < t2.Marks
where
    t2.Marks is null

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

This is what you can do , but in case a student has 2 max marks it will list only one of them

select
t1.Student,
t1.Subject,
t2.Marks from
table_name t1
join
(
  select 
  Student,
  max(Marks) as Marks
  from table_name 
  group by Student
)t2
on t2.Student = t1.Student AND t2.Marks = t1.Marks 
group by t1.Student;

If you need both max() marks subjects to be displayed in case there are, then the last group by needs to be changed to 

group by t1.Student,t1.Subject;

Here is a demo to illustrate both of them.

Upvotes: 0

Sorunome
Sorunome

Reputation: 508

You can combine multiple queries, so something like this:

SELECT * FROM students WHERE Student='Peter' AND Marks=( SELECT MAX(Marks) WHERE Student='Peter' )

Note that this is untested.

Upvotes: -1

xdazz
xdazz

Reputation: 160893

You could do like below:

SELECT 
  t1.Student, t1.Subject
FROM your_table t1
INNER JOIN (
  SELECT MAX(Marks) AS max_marks, Subject 
  FROM your_table
  GROUP BY Subject
) t2 ON t1.Subject = t2.Subject AND t1.Marks = t2.max_marks

Upvotes: 2

Related Questions