GROUP BY and ..not a group by expression
sen...@gmail.com
===========
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.
Carlos
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.
sen...@gmail.com
> Homework???
NO, dealing with a badly designed set of tables.
> There are meny solutions: subquery with max() and using analytics are
> just two of them.
Could please you show an example?
fitzj...@cox.net
You have the working subquery in your first example producing the
student id and max marks; you now need to take that and return the
data you want:
select id, name, testdate, marks
from students
where (id, marks) in
(SELECT ID, MAX(MARKS)
FROM STUDENTS
GROUP BY ID);
David Fitzjarrell
sen...@gmail.com
> select id, name, testdate, marks
> from students
> where (id, marks) in
> (SELECT ID, MAX(MARKS)
> FROM STUDENTS
> GROUP BY ID);
Wouldn't this return results from other dates too? though all we are
looking for is Max(marks) immaterial of date?
fitzj...@cox.net
It's up to YOU to restrict your result set according to your wants;
examples are simply that, EXAMPLES and should be taken as 'this is
generally how this is done'. I did not claim to post the answer, but
I did post a relevant response relative to the OP's original
assessment of the base query.
David Fitzjarrell
Ken Denny
Only the date that went with the max(marks). Of course if that same
student got that same max mark on two different dates then both of
them would show up in the results.
Brian Tkatch
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.