Thanks! this works. However its too slow to run on the actual table with
thousands of rows but i've managed to eliminate most of the rows by creating
a temporary table and then I run this query on the temporary table.

"oj" <nospam_ojngo@home.com> wrote in message
news:uqjta41dDHA.2320@TK2MSFTNGP12.phx.gbl...
> 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
> >
> >
> >
> >
>
>