Retrieving last 3 transactions for each customer 2

[VB400]

I have a Customer Transaction table where each customer has many records. How do I return just the last 3 transactions for each customer?

Say I have the following simple table

CustID Date
1234 01/15/02
1234 02/16/02
1234 02/17/02
1234 02/18/02
4567 01/15/02
4567 02/16/02
4567 02/17/02
4567 02/18/02

For each customer (1234, 4567) return just the February records (for example).

Thanks in advance! Tarek

 

[MeanGreen]

Here is one way... I am sure there is another way, but this would work:

set nocount on
declare @custid integer
declare loop_var cursor for
select distinct custid from table
order by custid

open loop_var

fetch next from loop_var into @custid
while @@fetch_status = 0
BEGIN
select top 3 custid,datedone
from table
where custid = @custid

fetch next from loop_var into @custid
END
close loop_var
deallocate loop_var


Since this is a cursor, it will be a little slow.

Hope this helps.

 

[VB400]

Thanks MeanGreen. I'll try that.

Is there anyway to do this in a SQL statement rather than an Stored Procedure? I will need to do something similar in Access. Tarek

 

[r937]

select CustID, trans_date
  from transactions XXX
 where trans_date in
       ( select top 3
                trans_date
           from transactions
          where CustID = XXX.CustID
       order by trans_date desc )
order by CustID, trans_date desc

you didn't really name your column "Date" did you?


rudy

http://rudy.ca/

 

[VB400]

Thanks Rudy,

No that was just sample data for the thread. You did make me smile though Tarek

 

[VB400]

Rudy, what is XXX? Tarek

 

[r937]

XXX is the correlation variable

that's a correlated subquery

for each row of the outer query, the subquery looks at all rows that match custID of that row


rudy