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]
