CODEWITHSUNDEEP
mysqljoinsubquery
parboy
parboy

Reputation: 67

Select only most recent entries from database

My database collects member renewal information including the date of renewal (payDate). Active members are determined by the payDate column which must be later than 9-30 of the previous year to be considered active. The club allows a member to renew as early as October of the prior year. So a member that renews in October of 2015 is an active member through December 31 of 2016. The problem I have is during the months of October thru December if I build a table of active members, I will get many duplicate entries because the current renewal and the previous renewal are technically both active. For the purpose of reporting, I want only the most recent renewal.

My current query, which gives duplicates, is as follows:

SELECT *
FROM membership
INNER JOIN memberDues ON ID = memberID
WHERE payDate >= '$year-10-01'
ORDER BY lastName, firstName"

I've tried this alternate query which doesn't work:

SELECT *
FROM membership
INNER JOIN (SELECT MAX(paydate) AS MAXpayDate
from memberDues)
memberDues ON ID = memberID
AND paydate = MAXpaydate
WHERE payDate >= '$year-10-01'
ORDER BY lastName, firstName

I've also tried this:

SELECT *
FROM (SELECT MAX(paydate)
FROM memberDues)
memberDues
INNER JOIN membership ON memberID = ID
WHERE payDate >= '$year-10-01'
ORDER BY lastName, firstName

I would appreciate any help.

Upvotes: 0

Views: 65

Answers (1)

JRD
JRD

Reputation: 1987

Get members that have paid after '$year-10-01' with most recent payDate.

select m.memberID, m.first_name, m.last_name, max(d.payDate) payDate
from   membership m
join   memberDues d on (d.memberId = m.id)
where  d.payDate >= '$year-10-01'
group by m.id, m.first_name, m.last_name;

Update:

To grab more column values from the member dues row.

select member_id, first_name, last_name, pay_date, amount, check_number from(
select member_id, first_name, last_name, pay_date, amount, check_number,
       case when @previous = member_id  then @rn := @rn + 1
            when @previous := member_id then @rn := 1
       end as rn
from   member m
join   memberDues md on (m.id = md.member_id)
join   (select @rn := 0) r
where md.pay_date >= '$year-10-01'
order by member_id, pay_date desc
) q
where rn = 1;

Upvotes: 1

Related Questions