July 22, 2010 at 11:14 am
I have a table that has these columns, clientnumber, code, plan, and eff_date, and more. Some records have the same clientnumber, code, and plan as another record but the eff_date is different. I would like to retrieve the record with the most recent eff_date).
client code plan eff_date additionalcol_1 additionalcol_2 more_cols
5678 300 abc 04/01/2007 anything1 anything 1 more1
5678 300 abc 09/01/2009 anything2 anything2 more2
5678 300 xyz 09/01/2009 anything3 anything3 more3
5678 301 nop 06/01/2008 anything4 anything4 more4
1234 300 abc 09/01/2009 anything5 anything5 more 5
I would like to retrieve records 2, 3, 4 and 5 but not 1 since it is the same clientnumber, code, and plan as record 2 but it has an older eff_date.
Any help on what type of join could do this would be appreciated.
Warm regards,
July 22, 2010 at 11:57 am
just use a subquery to determine the records you want to keep:
SELECTt.*
FROM@temp t
INNER JOIN (
SELECTclientnumber, code, plan, MAX(eff_date) as eff_date
FROM@temp
GROUP BY clientnumber, code, plan
) x ON t.clientnumber = x.clientnumber
AND t.code = x.code
AND t.plan = x.plan
AND t.eff_date = x.eff_date
_____________________________________________________________________
- Nate
July 22, 2010 at 12:31 pm
Thank you Nate. I don't know where my brain is today:-), that will do just fine.
July 22, 2010 at 1:18 pm
No worries, glad I was able to help.
_____________________________________________________________________
- Nate
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply