Joining Only on Max Date

I'm having so much trouble with this and I am unsure why. I need a join to a rate code table, but only grabbing the rate code from this table for each member with the max eff date (there are multiple rate codes in the table for each member but I only want the one with max(effdate)).

select distinct ek.memid, rs.ratecode, rs.description, rs.effdate 
from enrollkeys ek
inner join ratesuffix rs
on ek.ratecode = rs.ratecode

How do I add logic to only see the ratecodes listed per member with the max(effdate)? So if a member had 4 ratecodes, I only want to see the ones with the highest effdate.

Read more here:

Content Attribution

This content was originally published by Gregory Brauninger at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: