Preston Connors
Preston Connors

Reputation: 407

SELECT Rows Older Than Date Only If Row Does Not Have Row Newer Than Date

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

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can get the nicks 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 nicks that have scored in the past week.

Upvotes: 1

Related Questions