I've got a database driven community site, where customers can publish records. Each entry includes a date. Now Let me know who had been the very first user who had an entry count of 1000. Is possible having a SQL query or must i do this programatically, counting each publish before the limit is arrived at? Or other solutions?
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
It is really an example where I've found first user who reaches 3 messages. Hope it will help you.
SELECT user_id, time FROM table LIMIT 1 OFFSET 999 GROUP BY user_id ORDER BY time
I am unsure from the placing of ORDER BY in the finish there, may need a nested query or something like that since I am not using aggregation promptly after getting done an organization BY.
However the part you had been most likely searching for may be the LIMIT X OFFSET Y
I do not remember MySQL syntax, however i think you want to do something similar to this:
First, increase each entry its count (per user, obviously). There's most likely some MySQL specific method of doing the work, but when everything else fails, that you can do something similar to
SELECT Entry.*, (SELECT COUNT(*) FROM Entries E2 WHERE E.id<E2.id AND E.user=E2.user) AS EntryCount
With this done, choose the very first entry having a count of 1000, order through the entry time, and take its user.