Archives of the TeradataForum

Message Posted: Tue, 21 Apr 2009 @ 09:32:06 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Put your subquery as a table, and try using RANK.

Something like this (untested):

     SELECT
     Loan.LoanID,
     Tran_Amount.Amount
     FROM
     Loan,
     (SELECT LoanID, Amount, RANK() OVER (PARTITION BY LoanID ORDER BY PostDate DESC) as
     rank_amount FROM Tran WHERE tran_Type = 'D'
     and rank_amount = 1 ) Tran_Amount
     where Tran_Amount.LoanID = Loan.LoanID
     ;

Angela Carpenter

	<Prev	Next>		<<First	<Prev	Next>	Last>>