Last transaction by customer and account

arlf · #1 (**permalink**) 07-06-09, 22:08

TRANS_TBL (Customer Transaction Table) :
Cust_Id, Acct_Id, Trans_Id, Trans_Amount, ... , Trans_Date, Trans_Time
(table has no TimeStamp col)

I want to select the last transaction (based on Trans_Date and Trans_Time) for each customer and account.
A customer can have several accounts.

Any help ?

tonkuma · #2 (**permalink**) 07-06-09, 23:48

Code:

SELECT Cust_Id, Acct_Id, Trans_Id, Trans_Amount
     , ... 
     , Trans_Date, Trans_Time
  FROM (SELECT t.*
             , ROW_NUMBER()
               OVER(PARTITION BY Cust_Id, Acct_Id
                    ORDER BY Trans_Date DESC, Trans_Time DESC) rn
          FROM TRANS_TBL t
       ) s
 WHERE rn = 1
;

arlf · #3 (**permalink**) 07-08-09, 15:18

Thanks so much, Tonkuma. It was used like a subselect to extract data from a join with others tables.
saludos, ALRF.

Internet Services

Web Development

Digital Marketing

Consumer Tech