1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Query to find last entered payment

Discussion in 'Databases' started by rscott7706, Mar 23, 2021.

0
  1. #1
    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
     
    Solved! View solution.
    rscott7706, Mar 23, 2021 IP
  2. qwikad.com

    qwikad.com Illustrious Member Affiliate Manager

    Messages:
    7,151
    Likes Received:
    1,656
    Best Answers:
    29
    Trophy Points:
    475
    #2
    Use the member ID and the condition:

    ORDER BY MemberID DESC

    DESC is what will get the last payment of a particular member.
     
    qwikad.com, Mar 23, 2021 IP
  3. rscott7706

    rscott7706 Active Member

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    96
    #3
    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
     
    rscott7706, Mar 23, 2021 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #4
    use "PaymentID desc" or "PaymentDate desc" and put "limit 1" at the end
     
    sarahk, Mar 23, 2021 IP
  5. rscott7706

    rscott7706 Active Member

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    96
    #5
    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
     
    rscott7706, Mar 23, 2021 IP
  6. #6
    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):
     
    sarahk, Mar 23, 2021 IP
    qwikad.com likes this.
  7. rscott7706

    rscott7706 Active Member

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    96
    #7
    Thank you, I got it!!

    Fantastic..

    Ron Scott
     
    rscott7706, Mar 23, 2021 IP