At 9:14 -0400 10/4/04, [EMAIL PROTECTED] wrote:
What you are looking for is the MAX() of the Time column (most recent
entry)
select user, host, max(time) as time, count(user) as visits
from userlog where user!=''
group by user, host
order by time desc
limit 10;
The issue you ran into is caused by an non-standard SQL extension created
by MySQL. Its behavior is documented here:
http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html
<SOAPBOX ON>
IMHO, this extension of the SQL spec has caused more problems than it has
helped. I believe a better extension would have been to add a new GROUP BY
predicate like FIRST() or LAST() so that the behavior would be explicit
and controllable. It is even documented that the value returned is
potentially random
"Do not use this feature if the columns you omit from the GROUP BY part
are not unique in the group! You will get unpredictable results."
I know it's too late to take this behavior out of the older versions of
MySQL but is there any way to prevent its inclusion in future versions?
How about the ONLY_FULL_GROUP_BY option for the sql_mode variable?
http://dev.mysql.com/doc/mysql/en/Server_SQL_mode.html
Are there other options out there to "fix" this non-deterministic
behavior? I do not believe that educating the community to _avoid_ a
feature is a viable option. If it were, I do not think that we would need
to explain this strange behavior to new users as often as we do.
<SOAPBOX OFF>
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
