Reputation: 407
I have the below table where I would like to 'prune' out nicks who have not gained points in 1 week. I'm new to MySQL and I'm not sure how to best SELECT these rows. Your help is greatly appreciated!
Here is what I have so far that is not yielding correct results. The results this yields are nicks who have earned points at any time it seems.
SELECT * FROM points_log p1
INNER JOIN points_log p2 ON p1.nick = p2.nick
AND p1.dt < NOW() - INTERVAL 1 WEEK
WHERE p2.dt > NOW() - INTERVAL 1 WEEK LIMIT 10;
Here is the table:
mysql> describe points_log;
+-------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-----------------------+------+-----+---------+----------------+
| id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| nick | char(25) | NO | PRI | NULL | |
| amount | decimal(10,4) | YES | MUL | NULL | |
| stream_online | tinyint(1) | NO | MUL | NULL | |
| modification_type | tinyint(3) unsigned | NO | MUL | NULL | |
| dt | datetime | NO | PRI | NULL | |
+-------------------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
Upvotes: 1
Views: 118
Reputation: 1269953
You can get the nick
s who have scored in the past week using an aggregation:
SELECT pl.nick
FROM points_log pl
GROUP BY pl.nick
HAVING MAX(pl.dt) < NOW() - INTERVAL 1 WEEK;
I'm not sure what you want as final output, but this will return the nick
s that have scored in the past week.
Upvotes: 1