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
