[RESOLVED] need help with group by !!

unisabilly

suppose I have a table as follow :

name subject mark

Michael Math 60
Michael History 68
Ellen Math 72
Ellen English 58
Ellen Science 43
Jason Biology 42
Jason English 67
Jason Accounting 73
Jason Math 68
Julia History 76
Julia Science 54
Julia Biology 77

how can I select the name and subject of the best mark for every subject ?
ps. using mysql !!

Thanx in advance for help ~

bogu

SELECT * FROM table
GROUP BY subject
ORDER BY mark DESC

DESIGNGRAPHY

// Either 
SELECT * FROM tablename ORDER BY DESC or ASC
// ascending or descending order

bogu

Pixel Tiger wrote:

// Either 
SELECT * FROM tablename ORDER BY DESC or ASC
// ascending or descending order

He(She) didnt ask for ordering, he(she) asked for this:

unisabilly wrote:
how can I select the name and subject of the best mark for every subject ?

Lars_Berg

Well bogu, your query will not give the desired result either.

select  name,subject,mark
  from grades as g
 where mark in
  (select max(mark)
     from grades
    where grades.subject = g.subject)

bogu

Lars Berg wrote:
Well bogu, your query will not give the desired result either.

Well I understand he what to select the name for each subject (uniq) with the bigest mark ..

Ex.
Subject = Math
The bigest mark is 72
Student: Michael

Thanks Lars Berg for pointing me that,u are right, my first query is wrong (I writed whitout thinking, no excuse) ...

Subquery is supported only if u are using mysql 4.1 or bigger (I'm limited to mysql 4.0), this select will do on lower versions too...

SELECT max(mark) as max_mark, subject, name FROM `table`
GROUP BY subject

This select give u this result:

max_mark subject name

73 Accounting Jason
77 Biology Jason
67 English Ellen
76 History Michael
72 Math Michael
54 Science Ellen

unisabilly

bogu wrote:
Well I understand he what to select the name for each subject (uniq) with the bigest mark ..

Ex.
Subject = Math
The bigest mark is 72
Student: Michael

Thanks Lars Berg for pointing me that,u are right, my first query is wrong (I writed whitout thinking, no excuse) ...

Subquery is supported only if u are using mysql 4.1 or bigger (I'm limited to mysql 4.0), this select will do on lower versions too...
SELECT max(mark) as max_mark, subject, name FROM `table`
GROUP BY subject
This select give u this result:

max_mark subject name

73 Accounting Jason
77 Biology Jason
67 English Ellen
76 History Michael
72 Math Michael
54 Science Ellen

I'm Limited to Version 4.0 as well ~
there still some problem with the select statement
for example the higherst score for biology should be Julia 77 ..... not Jason 77 ~~ 🙂

Lars_Berg

select g1.name,g1.subject,g1.mark
from grades g1 join grades g2 on g1.subject = g2.subject
group by g1.name,g1.subject,g1.mark
having g1.mark = max(g2.mark)

bogu

It seems that Lars Berg find the solution, nice one Lars Berg, it seems I still have a lot to learn on mysql SELECT SYNTAX, I couldnt find the solution ...

unisabilly

Thanx to everyone specially to Lars Berg and bogu ~
It works perfectly ~ 🙂