How to get last payment made

I need to get the clientid, last payment date, and last payment amount. I have tried using MAX but this does not work. Can anyone see what I'm doing wrong? I get all payments and all dates. I'm summing the payment amount incase they made two payments on the same day, not likely but...

SELECT dbo.tblLoan.Client_ID,MAX(dbo.tblPayments.PaymentDate)AS [Last Pay Date],SUM(dbo.tblPayments.AmountPaid)AS [Last Pay Amt]
FROM dbo.tblLoanINNERJOIN
dbo.tblPaymentsON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_ID
GROUP BY dbo.tblLoan.Client_ID
HAVING (dbo.tblLoan.Client_ID = @.Client_ID)

Hi,

You can use "SELECT TOP 1" and "ORDER BY PaymentDate DESC"

Or you can use MAX(PaymentDate) to get the right record using that value in the WHERE clause. You may use CTE to get the code tidy.

|||

SELECT dbo.tblLoan.Client_ID,MAX(dbo.tblPayments.PaymentDate)AS [Last Pay Date],SUM(dbo.tblPayments.AmountPaid)AS [Last Pay Amt]FROM dbo.tblLoanINNERJOIN dbo.tblPaymentsON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDWHERE (dbo.tblLoan.Client_ID = @.Client_ID)AND dbo.tblPayments.PaymentDate = (SELECT TOP 1 p.PaymentDateFROM dbo.tblPayments pINNERJOIN dbo.tblLoan lON l.Loan_ID = p.Loan_IDWHERE l.Client_ID = @.Client_IDORDER BY p.PaymentDateDESC)GROUP BY dbo.tblLoan.Client_ID

|||

Thank you very much, addie