Getting Last payment information

[kjflash]

Hi,

Let's say I have a table containing payment information:

CustID, PaymentDate, PaymentAmount. Here is some sample data:


CustID Date Amount
01 01/01/07 50.00
01 02/01/07 100.00
01 12/31/06 275.00

I'm interested in the last payment date and the amount paid on that last payment date. Is there a quick/easy way to do this?

Thanks,

Keith

[ronverdonk]

Just sort it. Thanks to your funny date format you have to sort the date using the substr() function.

SELECT * FROM test 
  WHERE client=1
  ORDER BY 
    SUBSTR(date,7,2) DESC, 
    SUBSTR(date,4,2) DESC, 
    SUBSTR(date,1,2) DESC 
LIMIT 1;

Ronald :cool:

[kjflash]

Just sort it. Thanks to your funny date format you have to sort the date using the substr() function.

SELECT * FROM test 
  WHERE client=1
  ORDER BY 
    SUBSTR(date,7,2) DESC, 
    SUBSTR(date,4,2) DESC, 
    SUBSTR(date,1,2) DESC 
LIMIT 1;

Ronald :cool:

Thanks for your reply. I should have included more detail.

Let's say I have the customer list below:

01 John
02 Tom
03 Paul

And I have them making the following payments (Date formatting is unimportant)

01 03/12/06 $100.00
03 04/14/06 $42.50
03 12/31/06 $12.75
01 01/01/06 $167.00

I want a query that will show me the latest payment and payment amount for each Customer and return the following result set:

01 John 03/12/06 $100.00
02 Tom Null Null
03 Paul 12/31/06 $12.75

Thanks again for any help.

Keith

[Motoma]

Thanks for your reply. I should have included more detail.

Let's say I have the customer list below:

01 John
02 Tom
03 Paul

And I have them making the following payments (Date formatting is unimportant)

01 03/12/06 $100.00
03 04/14/06 $42.50
03 12/31/06 $12.75
01 01/01/06 $167.00

I want a query that will show me the latest payment and payment amount for each Customer and return the following result set:

01 John 03/12/06 $100.00
02 Tom Null Null
03 Paul 12/31/06 $12.75

Thanks again for any help.

Keith

What version of MySQL are you using?

[kjflash]

What version of MySQL are you using?

Version 5.0.27 -community-nt via TCP/IP