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

On 5 oct, 05:09, sen..._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.
Homework???

BAD design: ID & NAME doesn't follow 2FN. The date is relevant or not?

There are meny solutions: subquery with max() and using analytics are just two of them.

Cheers.

Carlos. Received on Fri Oct 05 2007 - 02:21:54 CDT