How to recover the last payment?

Question

I have the following table in my bank:

"financeiro
    financeiro_id INT AUTO_INCREMENT,
    financeiro_pagamento DATE,
    financeiro_pid INT,
    financeiro_valor INT,
";

In this table I record the payments that my system receives, then use the following Query to return me the values:

"SELECT *FROM login lo 
  inner join financeiro fi on lo.login_id = fi.financeiro_pid 
  Order by financeiro_pagamento";

This Query takes the financeiro_pid and joins with the table login to know who paid, only that I need each equal record of this table return only the person’s last payment. With this Query I’m taking all payments, but I just wanted the last payment from the user.

Obs.: financeiro_pid is where I register the user key.

Answer 1

The query below should do what you want. It will only take the most recent payments from each customer (MAX()):

SELECT * FROM financeiro fi 
   inner join login lo on lo.login_id = fi.financeiro_pid
WHERE
   fi.financeiro_pagamento = (SELECT MAX(fi2.financeiro_pagamento) FROM financeiro fi2 where fi2.financeiro_pid = fi.financeiro_pid)
ORDER BY
   financeiro_pagamento

Answer 2

Don’t use max()

Utilizes the last_insert_id() :

Reason:

https://stackoverflow.com/questions/8345399/is-it-conceptually-right-to-do-a-select-maxid-etc-for-finding-the-last-insert

Yes, there is some Relationship between max id and Most recent Insert, but consider the following:

what if the Most recently inserted Row was Deleted?