I would be very surprised if this query fails as you say it does. select user, max(time) as most_recent, count(user) as visits from userlog where user!='' group by user order by most_recent desc limit 10;
This represents the last 10 users to sign in, when they signed in last, and many times they have signed in so far. I may not know everything but unless you have some problem with the data so that your datetime field "time" is not acting the same for all visitors, this should work. Please describe why this data is wrong for your question. It is entirely likely I misunderstood what you wanted to find in your data. That would make my suggestions wrong (my apologies if so!!) Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Remi Mikalsen" <[EMAIL PROTECTED]> wrote on 10/04/2004 10:11:09 AM: > Thank you for the answer! However, it doesn't seem to solve my > problem. Running the query > without modifications repeated users (because of the "group by user, > HOST"). I removed the > HOST, and ran the query over again. Now it returned unique users, > but it still didn't return > the LAST login of them many logins for each user. > > Currently I am using a new implementation with two queries and PHP > in between. I won't > write all the code here, just explain the basics. > > 1. A query gets the total number of logins for each user > 2. PHP retrieves the logins and "orders" them with a perfect hash > - I create an array with indexes correspondig to usernames > - the values in the array are the numbers of logins > corresponding to each username > - this way I have practically no overhead when accessing the array > for example: $users['a_username'] = 10; /* 10 logins > by user a_username */ > 3. Another query gets all logins ordered by the time column, last first > 4. PHP reads the results. When a user is found that has more than 0 > logins in the $users > array, I print the user and the amount of logins, and set the value > in the $users array to 0. > This guarantees only printing a user once, and only printing the > last login due to ordering by > the time field. > > But this also has obvious drawbacks as to performance. I may have to > retrieve 10.000 rows > from the userlog table to get 10 unique users, or I might never have > 10 unique users in the > table, even with 1.000.000.000 records! I just never know. I also > have to make two queries, > which gives a small overhead. Finally, using scripting to interprete > intermediate results is > much slower than MySQL. All in all, this just doesn't seem to be a > very scaleable solution. > > > > Remi Mikalsen > > E-Mail: [EMAIL PROTECTED] > URL: http://www.iMikalsen.com > > > > On 4 Oct 2004 at 9:14, [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? > > 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] > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
