Re: Most Occurring Value

Mike Ginsburg wrote:
> 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) = ???
>
> Any and all help is appreciated. Thank you.

I'd say...

SELECT user_id, count(event_id) AS event_count FROM log GROUP BY
user_id ORDER BY event_count DESC LIMIT 1;

Or something to that effect.

Colin