CODEWITHSUNDEEP
mysqlsqljoin
John Doe
John Doe

Reputation: 2806

SQL: Get latest Payment from all customers?

im using Mysql 5.1 and given a table payments with columns customerid,paymentdate i need the all customers latest payment. so a table with entries

row, customerid, paymentdate
1    5           2011-03-01
2    5           2011-04-01
3    6           2011-01-01
4    7           2011-01-01
5    7           2011-02-01

should return rows 2,3,5 the query

select max(paymentdate),customerid from payments where customerid=5;

works which i tried to get into some kind of WHERE customer IN(...) but with no luck since it returns 2 columns not only 1

thanks

in the end the answers inspired me to find related information and solved the problem with the following query (as described in an articles comment)

select * from (select * from payments order by paymentdate desc) as p group by customerid;

same problem from mysql forum solutions from a related mysql article

Upvotes: 0

Views: 2877

Answers (3)

utsav kachhadiya
utsav kachhadiya

Reputation: 9

select max(payment_id),customer_id,payment_date from tbl_payment WHERE payment_type = "credit" GROUP by customer_id

Upvotes: -1

croat_kevin
croat_kevin

Reputation: 18

Another approach:

Select paymentdate,customerid from payments group by customerid order by paymentdate DESC;

Or even:

Select distinct(customerid),paymentdate from payments order by paymentdate DESC;

Adding a DESC to order by causes the most recent dates to be returned first.

Upvotes: -1

Gabriele Petrioli
Gabriele Petrioli

Reputation: 195992

You should group by the customerid.

select max(paymentdate),customerid from payments group by customerid;

Upvotes: 6

Related Questions