As Erland has suggested you should visit your indexing strategy. There is a
cost for creating the temp table and inserting data into it. With proper
index, this should be a breeze.

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



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