GROUP BY and ..not a group by expression

From: <send2r_at_gmail.com>
Date: Fri, 05 Oct 2007 03:09:44 -0000
Message-ID: <1191553784.674905.141540@19g2000hsx.googlegroups.com>

Hi, I have the following table

---

STUDENTS

---

ID NAME TESTDATE MARKS
=== ====== ========= ======

1       JOHN    2007-10-01     10
1       JOHN    2007-10-02     11
1       JOHN    2007-10-02       8
2       MARY   2007-10-01      10
2       MARY   2007-10-02       7

I want to list student with maximum mark he/scored immaterial of the test - date

The following query does it:
SELECT ID, MAX(MARKS)
FROM STUDENTS
GROUP BY ID RESULT -
ID MAX(MARKS)
=== ===========

1       11
2       10

Now, I want to show the NAME and TESTDATE in the result (remember, only for the max marks)
So I write:
SELECT ID, NAME, TESTDATE, MAX(MARKS)
FROM STUDENTS
GROUP BY ID XXXXXXX ERROR - not a group by expression - Agreed that TESTDATE & NAME are not in group by expression. I *don't* want to group by TESTDATE/NAME as it won't yeild the result I am looking for. What I am looking for is the same result from above with additional fields.

How to get around this problem? any help is highly appreciated.

THANKS IN ADVANCE. Received on Thu Oct 04 2007 - 22:09:44 CDT