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? 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> Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Remi Mikalsen" <[EMAIL PROTECTED]> wrote on 10/01/2004 06:41:32 PM: > Hello. > > I'm having a problem where I seem to need to order a table before > applying group by (or > distinct?) in my query. > > Here is a simplified table structure example: > ID USER HOST TIME > > ID = Primary Key > > I would like to do the following in ONE query, if possible: > I am looking to retrieve the LAST time 10 UNIQUE users were > registered in the table > (user+host+time). These users should be the last 10 people to be > inserted into the table > (each user can appear various times in the table, like in a log). At > the same time, I would like > to retrieve the TOTAL NUMBER of times each of these users appear in > the table, but this is > not very important. > > This was the query I adopted until noticing it had a severe problem: > > select user, host, time, count(user) as times > from userlog where user!='' > group by user > order by time desc > limit 10; > > The problem is that the TIME associated with each person isn't the > LAST TIME a registry > was done for the user. This makes me think that I might need to > order the TIME column > before doing the GROUP BY, but I do no know how (and it might not > even be the solution to > the problem!). > > I do not know if I managed to express myself very well, but if > anyone is willing to help, I > would of course clarify things if necessary. > > > Remi Mikalsen > > E-Mail: [EMAIL PROTECTED] > URL: http://www.iMikalsen.com > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
