You are looking for a minimum value _within_ a maximum set. In this case,
that will take two processing steps:
DECLARE TEMPORARY TABLE tmpScores
SELECT classid, Max(score) as topscore
FROM tablename_goes_here
GROUP BY classid;
SELECT t.classid, t.Min(studentid), ts.topscore
FROM tablename_goes_here t
INNER JOIN tmpScores ts
ON ts.classid = t.classid
AND ts.topscore = t.score
GROUP BY t.classid, ts.topscore;
DROP TABLE tmpScores;
That should answer the question "For each class, what is the smallest
student ID that scored highest for that class". You will need to replace
"tablename_goes_here" with the actual name of your table.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Haitao Jiang <[EMAIL PROTECTED]> wrote on 08/10/2004 02:24:29 AM:
> Hi,
>
> If I want to find out highest score student from each class, how can I
> do that in MySQL? Assume the table looks like:
>
> classId INT,
> studentId INT,
> score INT
>
> In the case of multiple students from the same class has the same
> highest score, I would like to get the first one whose studentId is
> the smallest. I tried to use sub-query, but in the case of students of
> same highest score in the same class, they all get returned - I only
> want one from each class.
>
> Is it possible?
>
> Thanks a lot
>
> HT
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
