I have 2 tables I need to join them show the latest record/date as well as a count of records. Wondering the best way to do it. I started to write a simple count/group by but it just kept not grouping since the timestamps don't match and the count is always just 1. here is what i currently have:
Select Top 9 CommentText, '/iPolicy/'+PolicyLink+'-'+CAST(HH_iPolicy_Data.iPolicyID as varchar)+'.htm' as link,CommentDate,COUNT(HH_iPolicy_Data.iPolicyID) as theCount
from HH_iPolicyComment_Data, HH_iPolicy_Data
Where HH_iPolicy_Data.iPolicyID = HH_iPolicyComment_Data.iPolicyID
Group By '/iPolicy/'+PolicyLink+'-'+CAST(HH_iPolicy_Data.iPolicyID as varchar)+'.htm',CommentDate,CommentText
Order By CommentDate Desc;
Tables [With colums of interest]:
The outcome I am looking for is to group the comments of the iPolicy showing the count (of total comments), the latest comment text and the datetime of the latest comment.