Select statement that will only give me the latest (max) date where multiple records exist for each ID
Posted on 2012-12-28
I have a table that shows multiple records per ID, it tracks the status of a loan in process and many times people will record the same status more than one time. I'm looking for a select statement that will give me just the latest date that the loan was moved to it's current status. So in other words the max date for the max status but I can't use an aggregate function like (MAX).
for example my current select statement;
TOP (100) PERCENT dbo.setups_loanstatus.statusdescription AS [Latest Status], dbo.loan_main.loanid, dbo.setups_loanstatus.statusorder, dbo.loan_status.statusid,
dbo.loan_status.datefirstentered, dbo.loan_main.lenderdatabaseid, dbo.loan_main.loanrecordid
FROM dbo.loan_main INNER JOIN
dbo.setups_loanstatus ON dbo.loan_main.statusid = dbo.setups_loanstatus.statusid LEFT OUTER JOIN
dbo.loan_status ON dbo.setups_loanstatus.statusid = dbo.loan_status.statusid AND dbo.loan_main.lenderdatabaseid = dbo.loan_status.lenderdatabaseid AND
dbo.loan_main.loanrecordid = dbo.loan_status.loanrecordid
ORDER BY dbo.loan_main.loanid DESC, dbo.setups_loanstatus.statusorder DESC
gives me the correct date for the current status but if the loan has been in that status more than once I get two records for that Loan ID.