MYSQL: Select MAX date from multiple columns

Question

I'm having trouble with a SQL query. This is my purchase table:

id       email                status       date
1        [email protected]     confirmed    2014-10-01
2        [email protected]     confirmed    2014-10-02
3        [email protected]     pending      2014-10-02
4        [email protected]     pending      2014-10-02
5        [email protected]     pending      2014-10-03
6        [email protected]     pending      2014-10-03
7        [email protected]     pending      2014-10-04
8        [email protected]     pending      2014-10-04
9        [email protected]     pending      2014-10-05

I want to select the most recent pending purchase for each email, so the result I'm expecting is:

id      email                 status       date
6       [email protected]      pending      2014-10-03
7       [email protected]      pending      2014-10-04
9       [email protected]      pending      2014-10-05

Any ideas how I can achieve this?
Thanks in advance.

Answer 1

You can do this with a subquery:

select p.*
from purchase p join
     (select email, max(id) as maxid
      from purchase
      where status = 'pending'
      group by email
     ) pmax
     on p.id = pmax.maxid;

This assumes that "most recent" is the one with the largest id.