Aggro wrote:
Please correct me if you think the subquery works correctly in this case
or with 4.1, I don't have much experience with those.
No, you're absolutely right! I am very sorry to have suggested the
solution, since it doesn't work.
This points out the trouble with this type of aggregation query in SQL.
When using GROUP BY, the only fields that are safe to reference in the
select-list but not in an aggregation function are columns named in your
GROUP BY statement.
In other words, in this case:
SELECT A, B, MAX(C)
FROM table
GROUP BY A
The value of A is invariant in the grouping, and the value of C is
well-defined as the maximum value of field C in that grouping. But the
value of B is ambiguous.
In some RDBMS implementations , it's actually a semantic error to execute
a query with this ambiguity, and the query fails if you try to do that.
MySQL permits you (wrongly, in my opinion) to execute this type of
query, and it chooses a more or less arbitrary value to return for those
fields. As Aggro points out, it seems to choose the "first" row in the
group (but this is a coincedence of implementation, and not due to any
rule, since there is no such thing as an implicit ordering of rows in a
table).
So for Mick's solution, we can only use fields in the subquery that are
part of the aggregate expression, or are referenced in the GROUP BY
clause. I'm going to make an assumption that the id field is used in
such a way that a greater id value for a given guest is guaranteed to
correspond to a greater value for time_in. That is, MAX(time_in) always
occurs on the same row as MAX(id) for a given guest.
SELECT gsub.fname, gsub.lname, MAX(gsub.id)
FROM guestbook AS gsub
GROUP BY gsub.fname, gsub.lname
This gives us the max comment id per guest, which is also known to be
the most recent entry for that respective guest. Now make a
comma-separated string of all the values of id returned by the above
query, and use it when building the following query (we have to build
the SQL query as a string, because simple parameter substitution with ?
doesn't support lists of values).
SELECT g1.fname, g1.lname, g1.comments
FROM guestbook AS g1
WHERE g1.id IN ( $idlist )
This should work, doesn't violate the grouping-column rule, and doesn't
require subqueries.
Regards,
Bill K.