MySQL Forums
Forum List  »  Performance

How to get the oldest N rows per user with the totol row count bigger than some threshold?
Posted by: Eric Kwon
Date: January 28, 2008 10:28AM


I have a log table which record user's activity.

Simple table design as follows.

id int unsigned not null auto_increment,
user_id int unsigned not null,
date int unsigned not null,
KEY (user_id, date)
) engine=innodb

I want to get the oldest N(for example count(id) - 2) rows per user whose total record count exceeds certain limit (for example 2). The N per user may vary according to the total record count per each user.

For example, the rows of table log may be as follows.
id, user_id, date,
1 1000 20071102
2 1001 20071103
3 1000 20071104
4 1000 20071101
5 1000 20071108
6 1000 20071109
7 1001 20071102
8 1001 20071109
9 1002 20071107
10 1001 20071114

I want to get the result set like following.

4 1000 20071101
1 1000 20071102
3 1000 20071104
7 1001 20071102
2 1001 20071103

normal gourp by clause will return only one row per each group.
So it is hard to do this kind of query.

select id, user_id, regdate from log as l inner join (select user_id, .. FROM log GROUP BY user_id HAVING COUNT(id) as cnt > 2) as s on (l.user_id = s.user_id) ) order by user_id, regdate limit cnt-2

This join does not achieve my result set, because limit cannot limit result set per user_id.

Is there any efficient way to achieve my purpose?

Thanks in advance.

Options: ReplyQuote

Written By
How to get the oldest N rows per user with the totol row count bigger than some threshold?
January 28, 2008 10:28AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.