SQLTeam.com | Weblogs | Forums

Getting the most recent record

#1

Hello.

I am trying to get the most recent record for the subquery below. However, I am at a loss to get the most recent record as there are duplicates. How can I set a condition that gets the most current last modified date to make sure I am getting the most recent? Thanks.

BK_Old_Loan_Type=(Select Case When lp.ItemName='DLOLNTYPE' AND Then lp.LoanPropertyValue Else 'None' End From LoanProperty lp Join Loan ln On ln.MemberNumber=lp.MemberNumber AND ln.LoanNumber=lp.LoanNumber Where lp.ItemName='DLOLNTYPE' AND ln.LoanType='48' AND lp.ItemName='DLOLNTYPE')

#2

Can you post the duplicate data here, not sure if it is confidential stuff.
You might be able to use row_number partition command but we would have to see
the data or explain the duplicate scenario a bit more?

#3

It's weird because if I run the query as a standalone, there are no duplicates.

However, the subquery in another query provides this result:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.