Sir, try
SELECT g1.name, g1.score, g1.id
FROM grades g1, grades g2
WHERE g1.name = g2.name
GROUP BY g1.name, g1.score
HAVING Max(g1.score) = Max(g2.score);
Bob Hall
>Q: I have the following table "grades".
>
>+--------+--------+--------+
>| name | score | id |
>+--------+--------+--------+
>| John | 10 | 1 |
>+--------+--------+--------+
>| John | 20 | 2 |
>+--------+--------+--------+
>| John | 8 | 3 |
>+--------+--------+--------+
>| Carl | 10 | 4 |
>+--------+--------+--------+
>| Carl | 15 | 5 |
>+--------+--------+--------+
>| Mary | 6 | 6 |
>+--------+--------+--------+
>
>
>I'd like to select a single name, maximum score, as well as the
>corresponding id number and put them in order from highest to lowest and end
>up with:
>
>+--------+--------+--------+
>| name | score | id |
>+--------+--------+--------+
>| John | 20 | 2 |
>+--------+--------+--------+
>| Carl | 15 | 5 |
>+--------+--------+--------+
>| Mary | 6 | 6 |
>+--------+--------+--------+
>
>I tried using:
>
>SELECT name, id, max(score) FROM grades GROUP BY name ORDER BY score DESC
>
>At first I was pretty happy with this, but I realized that although the name
>and score where the values I was looking for, the id didn't always
>correspond to the record I wanted.
>
>Any ideas????
>
>
>---------------------------------------------------------------------
>Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail
><[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
