select customerid,paymentid,[date],amount
from payments p1
where paymentid=(select top 1 paymentid from payments p2 where
p2.customerid=p1.customerid order by [date] desc, paymentid desc)


--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net



"MAB" <dsfoalsdfsdfadouisdf@yahoo.com> wrote in message
news:bjmcjb$klvrf$1@ID-31123.news.uni-berlin.de...
> 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
>
>
>
>