mysql query sub selecting group by

Question

I have a table described below

mysql> describe payments;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| name           | varchar(128) | NO   |     | NULL    |                |
| email          | varchar(128) | NO   |     | NULL    |                |
| txn_id         | varchar(19)  | NO   |     | NULL    |                |
| payment_status | varchar(20)  | NO   |     | NULL    |                |
| auth           | varchar(40)  | NO   |     | NULL    |                |
| expired_at     | datetime     | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

It is possible that I could have 2 entries such as:

name: chris|expires at: 2012-01-01|email: me@chrismuench.com
name: chris|expires at: 2014-01-01|email: me@chrismuench.com

I want to do a query that I find all expired users based on downloads. But there could be multiple entires for the same email address. In the above case this person should NOT show up in the expired list.

It seems like I want to do a GROUP BY email but somehow filter out expiration if it is > NOW()

Answer 1

Here is what you would have to do. I haven't used datetime functions in a bit so you'll have to double check it for correctness. I just assumed NOW() was a function.

Find the set of unexpired users, then subtract it from the entire set of users.

select * from table where email not in (select email from table where expired_at > NOW());

Answer 2

It would seem that for each unique user you want the maximum date and to check whether that is before NOW() right? So:

SELECT name, MAX(expired_at) as latest_expired FROM payments WHERE lastest_expired < NOW() GROUP BY name;