I want the sum of the last payments (amount) for all customers. The last
payment is with one with most recent date. And if there are more than one
payment on the most recent date then the one with the higher paymentid is
the last payment. for example in the given data the insert statement that
starts with capital I is the last payment of that customer. The correct
answer should be 2100 as given below. both queries by Erland and Anith give
the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from
both queries since right now I want current sum (not till some date). So
what should be the right query.

Thanks again for the help.

create table payments (
paymentid int,
customerid int,
amount int,
date datetime
)

insert payments values (1, 1, 100, '1/1/03')
insert payments values (2, 1, 200, '2/28/03')
Insert payments values (3, 1, 500, '5/15/03')

insert payments values (4, 2, 400, '1/16/03')
insert payments values (9, 2, 800, '4/30/03')
insert payments values (5, 2, 200, '6/15/03')
Insert payments values (6, 2, 900, '6/15/03')

insert payments values (7, 3, 700, '3/1/03')
insert payments values (10,3, 300, '7/10/03')
Insert payments values (8, 3, 600, '9/1/03')

insert payments values (11,4, 300, '8/1/03')
insert payments values (12,4, 400, '9/10/03')
Insert payments values (13,4, 100, '9/10/03')


customerid lastpayment amount

1 3 (on 5/15/03) 500
2 6 (on 6/15/03) 900
3 8 (on 9/1/03) 600
4 13 (on 9/10/03) 100

========
Result => 2100