Showing their firstPayment and latestPayment on the same row

[busboy]

This query works when I include the "limit 1", but the data is not correct. I'm trying to show their firstPayment and their latestPayment for those people who upgraded their membership this time last year. Am I getting close?

Thanks!

select date_format(payDate, '%m-%d-%Y') as firstPayment, u.uID, concat(u.firstname,' ', u.lastname) AS name, 
u.subscription, (select max(payDate) from payments group by uID limit 1) as latestPayment
from payments p
inner join users u
on p.uID = u.uID
where frequency = 'Yearly'
and payDate like '2011-05%'
order by firstPayment;

[guido2004]

How about (didn't test it):

select 
    date_format(min(payDate), '%m-%d-%Y') as firstPayment
  , u.uID
  , concat(u.firstname,' ', u.lastname) AS name
  , u.subscription
  , max(payDate) as latestPayment
from payments p
inner join users u
on p.uID = u.uID
where frequency = 'Yearly'
GROUP BY u.uID
HAVING min(payDate) like '2011-05%'
order by firstPayment;

I actually don't know if you can do a LIKE on a date like that? But if it worked in your query, I guess it'll work in this one too

[busboy]

That worked perfectly. Thank you!