Sql grabbing most recent record

Question

I'm currently using DBISAM sql compiler. It's very very identical to ms sql compiler, the only difference is that I can't have any nested join statements.

The query below is a nested query that grabs the most recent loan record and the rate. I'm wondering if there's another way I can write this without the nested select statement.

select * from 
(select Loan_Id, Max(effectiveDate) as EffectiveDate from InterestTerms 
group by Loan_Id) as Y
join InterestTerms as X on Y.Loan_Id = X.Loan_Id and Y.EffectiveDate = X.EffectiveDate
order by Y.Loan_Id

Can you use WITH? https://msdn.microsoft.com/en-us/library/ms175972.aspx — TAMTAM, Mar 24 '15 at 14:14
Nope, I was thinking of having something like http://stackoverflow.com/questions/6617347/selecting-most-recent-record answer by Bohemian — Master, Mar 24 '15 at 14:26

score 0 · Answer 1 · 2015-03-24T14:41:36.603

0

You could try the following:

select 
    X.*
FROM
    InterestTerms AS X
WHERE
    X.effectiveDate IN (
     select 
          Max(Y.effectiveDate) as MaxED  
     from 
          InterestTerms as Y
     WHERE
          Y.Loan_Id = X.Loan_Id
    ) 
order by 
    X.Loan_Id

(UPDATED)

edited Mar 24 '15 at 14:41

answered Mar 24 '15 at 14:30

I'm sorry - I didn't notice the lack of `TOP` support in DBISAM - please find my updated answer. – Mar 24 '15 at 14:42
No problem, I can't catch a break with DBISAM... `DBISAM Engine Error # 11949 SQL parsing error - Expected column name but instead found Y.Loan_Id in SELECT SQL statement ` Thanks for the help though, greatly appreciated – Master Mar 24 '15 at 14:52
No problem. I hope you will find the solution. If I knew anything more I will write. Regards. – Mar 24 '15 at 14:55
Do you think this can be done using a left outer join? – Master Mar 24 '15 at 15:05

Sql grabbing most recent record

1 Answers1