MYSQL Distinct help needed

Question

Need help in generating report from DB getting Distinct value of Balance and getting the only oldest payment date per month (or not displaying the latest payment date per month)

Sample below is the should-be report. I only deleted the latest rows to make a sample of my objective.

here's the code:

SELECT payment_date,transaction_code,amount,interest_amount,
balance‌,source FROM m_loan_payment 
WHERE loan_no = '13492' ORDER BY payment_date DESC

/*getLoanPayments*/ SELECT payment_date,transaction_code,amount,interest_amount,balance,source FROM m_loan_payment WHERE loan_no = '13492' ORDER BY payment_date DESC — Jherom Ocado De Jesus, Nov 09 '17 at 00:51
Duplicate: https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?noredirect=1&lq=1 — Barmar, Nov 09 '17 at 01:13

score 0 · Accepted Answer · answered Nov 09 '17 at 01:31

Here is one method, assuming the date is stored as a date:

SELECT lp.*
FROM m_loan_payment lp
WHERE loan_no = 13492 AND
      lp.payment_date = (SELECT MIN(lp2.payment_date)
                         FROM m_loan_payment lp2
                         WHERE lp2.loan_no = lp.loan_no AND
                               YEAR(lp2.payment_date) = YEAR(lp.payment_date) AND
                               MONTH(lp2.payment_date) = MONTH(lp.payment_date) 
                        )
ORDER BY payment_date DESC;

Similar logic can be used if the payment date is stored as a string or a number.

MYSQL Distinct help needed

1 Answers1