This is what it says about the ONLY_FULL_GROUP_BY option...
ONLY_FULL_GROUP_BY
Don't allow queries that in the GROUP BY part refer to a not selected column. (New
in MySQL 4.0.0.)
I don't see how this can help me, because in the GROUP BY part I do refer to a
selected
column. The problem is that the Internal Ordering made by GROUP by seems to be
somewhat unpredictable. Besides, I depend on my Hosting Company that uses MySQL
version 4.0.20-standard. It seems that only from version 4.1 it is possible to apply
this option
on a single user basis - I do not think that my hosting company would change the GROUP
BY behaviour for all clients on my server because of me.
If I missed the whole point of the suggestion... please let me know!
Remi Mikalsen
E-Mail: [EMAIL PROTECTED]
URL: http://www.iMikalsen.com
On 5 Oct 2004 at 9:07, Paul DuBois wrote:
> 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
