Hi, scoured the forum, using MAX and LAST to see if I could find an answer. No luck for my targeted situation. I have a table (view) that combines two tables: members payments Works fine.. But, I need to create a query that will list only the last payment made by a member so a query can be created for periodic payment notices. Tables: members Field: MemberID payments Fields: MembersID (foriegn key to MemberID) PaymentDate I hope I gave enough information to get started, thanks for considering. Ron Scott
Use the member ID and the condition: ORDER BY MemberID DESC DESC is what will get the last payment of a particular member.
I think query the field might be MembersID (since it is in the payments table). And, it still would result in all payments, just sorted differently, not exclusively the last (most recent) payment. Would I then do some sort of query on that to get most recent payment? Thanks Ron
I certainly did not frame this request well. The above situation spits out only one record. I should have said "most recent record for all members" not just one member. I need the list to spit out all member payments, which may be one or more, by most recent date submitted. Then I can do a query that samples by current, who are due to renew their membership. Sorry not to be more clear on this!! Ron Scott
aaah, well that's quite a different challenge. you've no doubt discovered the quirks of max() doing some really strange things to records. I created a SQL fiddle at http://sqlfiddle.com/#!9/c71510/2 and this is the query I ran - assuming that payments are added in date order so that the id mirrors a sort key. SELECT m.id, m.name, p.paymentDate, p.amount FROM members AS m LEFT JOIN (SELECT membersId, MAX(id) AS id FROM payments GROUP BY membersId) AS temp ON m.id = temp.membersId LEFT JOIN payments AS p ON temp.id = p.id Code (markup):