>I am wondering how I can get the record for a certain MemberID; there are several records of termination for this one person, and they are valid because the person had insurance coverage at various points in time and eventually allowed his coverage to Terminate for one reason or another. I have to fin the record having the MAXIMUM Termination_Date.
>
>I tried using the MAX(Term_Date) function in the HAVING clause but it doesn't have enough arguments, and doesn't seem to apply there.
>
>It seems that MAX in FoxPro and T-SQL are a bit different.
One possibility:
select MI.* from MemberInfo MI where DateFld = (select max(DateFld) from MemberInfo MI1 where MI1.MemberID = MI.MemberID)
For more details about various approaches for this problem take a look at these blog posts
Optimizing TOP N per Group Queries - blog by Itzik Ben-Gan explaining various optimization ideas
Including an Aggregated Column's Related Values - Erik and mine blog presenting several solutions of the problem with explanations for each
Including an Aggregated Column's Related Values - Part 2 - my blog post with use cases for the previous blog
If you need to get a record for just one particular member, then Brandon's query is correct, e.g.
select top 1 * from MemberInfo where memberID = @lnID order by DateFld DESC
If it's not broken, fix it until it is.
My Blog