Volkan YAZICI escreveu:
> Mike Ginsburg <mginsburg@collaborativefusion.com> writes:
>> There is probably a really simple solution for this problem, but for
>> the life of me I can't see to think of it. I have three tables
>>
>> --contains u/p for all users in the site
>> TABLE users (user_id INT primary key, username VARCHAR(50), password TEXT)
>> --list of all possible events (login, logout, timeout)
>> TABLE events (event_id INT primary key, event VARCHAR(255))
>> --logs the activity of all users logging in/out, etc
>> TABLE log (log_id INT primary key, user_id INT REFERENCES users,
>> event_id INT REFERENCES event);
>>
>> How would I query to find out which user has the most activity?
>> SELECT user_id, COUNT(event_id)
>> FROM log
>> GROUP BY (user_id)
>> HAVNG COUNT(event_id) = ???
>
> SELECT user_id, max(count(event_id))
max(count() is invalid.
aggregate function calls may not be nested
> FROM log
> GROUP BY user_id;
>
> or
>
> SELECT user_id, count(event_id)
> FROM log
> GROUP BY user_id
> ORDER BY count(event_id) DESC
> LIMIT 1;
If more than 1 user has the most activity only one is listed.
Try:
SELECT user_id, COUNT(event_id)
FROM log
GROUP BY (user_id)
HAVING COUNT(event_id) = (SELECT max(l.ct) FROM
(SELECT count(event_id) AS ct FROM log GROUP BY user_id) AS l)
ORDER BY user_id;
Osvaldo
