Re: GROUP BY and ..not a group by expression

On Fri, 05 Oct 2007 03:09:44 -0000, send2r_at_gmail.com wrote:

>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.
It is not a problem. The requirement is illogical.

For Name, assuming Id and Name are always the same, Name can be added to the GROUP BY clause, and thus SELECTed, without any adverse implications, being it will split it no more than Id already has.

MAX(Marks), however, is not a record. instead, it is the maximum value from a set of records. There may even be multiple records with that value, which means that TestDate is a set of records, not a single value. So, either a sub-query is required (to choose one of any of the matching records) or an analytical function, as suggested.

B. Received on Mon Oct 08 2007 - 11:07:07 CDT