Showing their firstPayment and latestPayment on the same row
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?
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;