CODEWITHSUNDEEP
mysqldatabase
chiborg
chiborg

Reputation: 28104

Determine "first user who did x" from aggregate values

I have a database driven community web site, where users can post entries. Each entry has a date. Now I'd like to know who was the first user who had an entry count of 1000. Is this possible with a SQL query or do I have to do that programatically, counting each post until the limit is reached? Or any other solutions?

Upvotes: 4

Views: 49

Answers (3)

Nicola Cossu
Nicola Cossu

Reputation: 56397

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 where I find first user who reaches 3 messages. Hope it helps you.

Upvotes: 2

Jean-Bernard Pellerin
Jean-Bernard Pellerin

Reputation: 12670

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

I'm not sure of the placing of ORDER BY at the end there, might need a nested query or something since I'm not using aggregation on time after having done a GROUP BY.

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

Upvotes: 1

zmbq
zmbq

Reputation: 39023

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

First, add to each entry its count (per user, of course). There's probably some MySQL specific way of doing it, 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

With that done, select the first entry with a count of 1000, order by the entry time, and take its user.

Upvotes: 0

Related Questions