Most Recent Records

Ok, I was able to find the most recent Effective date and it works well. The only problem I am having is with the CarrierName. See there are different Carriers with so then it makes us have Mult effective dates. I need to pull the most recent effective date with carrier. So, I though to max the pcsiNumber2, but that didn’t work. Do you have any other ideas? Let me know.


Select Distinct
--PCSNumber,
PCSINumber1,
max(PCSINumber2) as mostrecentPCS,
LastName,
FirstName,
State,
--InsuranceCarrier,
CarrierName,
MAX(CoverageEffectiveDate)as MostRecentEff,
MAX(TerminationDate) as MostRecentTerm,
AmtPerClaim,
Aggreg

FROM VW_Medical_Malpractice_Project

Where
State = 'PA'


Group By PCSINumber1, LastName,FirstName,State,CarrierName,AmtPerClaim,Aggreg

Order By PCSINumber1


Example


000043010003MUSSER WILLIAM American Professional Agency2002-06-01

000043010002MUSSERWILLIAM Columbia Casualty2000-02-20

000043010001MUSSERWILLIAMMCIC Vermont Inc.1998-07-01

000043010004MUSSERWILLIAMProMutual Insurance Co.2005-05-16

000043010005MUSSERWILLIAMTri-Century Insurance Company2007-01-01


So, what I need is the most recent effective date which would be 2007-01-01, which I have but what is not letting me do this is the names, the 5ith column. I was trying to use the 2nd column to use the max , but it is not working. What am I doing wrong. I need the whole last row, but that number 0005 can change because there may only be 2 dup records.