Re: Most Occurring Value

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))
  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;


Regards.

P.S. It'd be better if you can send such questions to pgsql-sql mailing
     list.