Need to get a specific record count Most recent 5 with detail 1

[DoctorV3774]

I have a fairly simple SQL Statement. I need the Most recent 5 records in descending date order by Agency_Diary_Date_of_Contact for each Producer_code to display. Problem I am having when I use Top 5 in the select statement is that it only shows the most recent 5 for the first producer in the recordset. How can I adjust this so that I get the most recent 5 only for each producer withh all detail. (Some Producers may only have 4 or less recods I just need up to the 5 most recent)

Thanks


SELECT Agency_Diary.Producer_Code, Agency_Diary.Agency_Diary_Date_of_Contact, Agency_Diary.Agency_Diary_ID, Agency_Diary.Agency_Diary_Type_of_Contact, Agency_Diary.Agency_Diary_Agency_Participants, Agency_Diary.Agency_Diary_GAIC_Participants, First(Agency_Diary.Agency_Diary_Notes) AS FirstOfAgency_Diary_Notes
FROM Agency_Diary
GROUP BY Agency_Diary.Producer_Code, Agency_Diary.Agency_Diary_Date_of_Contact, Agency_Diary.Agency_Diary_ID, Agency_Diary.Agency_Diary_Type_of_Contact, Agency_Diary.Agency_Diary_Agency_Participants, Agency_Diary.Agency_Diary_GAIC_Participants
ORDER BY Agency_Diary.Producer_Code, Agency_Diary.Agency_Diary_Date_of_Contact DESC;

 

[PHV]

SELECT Producer_Code, Agency_Diary_Date_of_Contact, Agency_Diary_ID, Agency_Diary_Type_of_Contact, Agency_Diary_Agency_Participants, Agency_Diary_GAIC_Participants, Agency_Diary_Notes
FROM Agency_Diary A
WHERE Agency_Diary_Date_of_Contact IN (SELECT TOP 5 Agency_Diary_Date_of_Contact FROM Agency_Diary B WHERE B.Producer_Code = A.Producer_Code ORDER BY 1 DESC)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244