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

Question

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.

what if a student has same marks in 2 subjects and these are max which subject will be considered with max() mark. — Abhik Chakraborty, May 29 '14 at 09:35
possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — piotrm, May 29 '14 at 09:36

score 2 · Answer 1 · answered May 29 '14 at 09:32

2

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

answered May 29 '14 at 09:32

xdazz

158,678
38
247
274

score 0 · Answer 2 · answered May 29 '14 at 09:47

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.

score 0 · Answer 3 · answered May 29 '14 at 09:55

0

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

answered May 29 '14 at 09:55

Hieu Vo

3,105
30
31

score 0 · Answer 4 · answered May 29 '14 at 10:38

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 )

score -1 · Answer 5 · answered May 29 '14 at 09:33

-1

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.

answered May 29 '14 at 09:33

Sorunome

478
4
8

3

The query should be universal, not hard-coded. Also its strange and does not return the required output. – DNac May 29 '14 at 09:35

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

5 Answers5