Define the "first user to make x" from the aggregate values

Question

I have a database-enabled community website where users can post. Each entry has a date. Now I would like to know who was the first user to have 1,000 records. Is this possible with an SQL query or should I do this programmatically, counting every message until the limit is reached? Or any other solutions?

Answer 1

create table posts ( id int not null auto_increment primary key, user_id int, post_date date ) engine = myisam; insert into posts (user_id,post_date) values (1,'2011-03-02'), (1,'2011-04-10'), (1,'2011-11-13'), (2,'2011-03-02'), (2,'2011-03-02'), (3,'2011-01-01'), (3,'2011-01-02'), (3,'2011-01-03'); select * from ( select posts.*, @num := if(@user_id = user_id, @num + 1, 1) as rownum, @user_id:=user_id as uid from posts,(select @user_id := 0, @num := 1) as t order by user_id asc, post_date asc) as tab where rownum = 3 order by post_date limit 1 

This is an example when I find the first user that reaches 3 posts. Hope this helps you.

Answer 2

 SELECT user_id, time FROM table LIMIT 1 OFFSET 999 GROUP BY user_id ORDER BY time 

I'm not sure about placing ORDER BY at the end, it may require a subquery or something, because I do not use aggregation on time after doing GROUP BY.

But the part you were probably looking for is LIMIT X OFFSET Y

Answer 3

I don't remember the MySQL syntax, but I think you should do something like this:

First add your account to each entry (per user, of course). There is probably some way to execute MySQL, but if all else fails, you can do something like

 SELECT Entry.*, (SELECT COUNT(*) FROM Entries E2 WHERE E.id<E2.id AND E.user=E2.user) AS EntryCount 

After that, select the first record with the quantity 1000, order by the time of entry and take your user.