question

SELECT account, MAX(ActDate)... ...GROUP BY Account (and any other fields selected) Might be a starting point.

Aside from the aggregation that @ThomasRushton suggests, you could also use a windowing function in a subquery, like so: select b.account, ap.InstallOSName, ay.ActDate, b.offerid, c.parentcommunityid, c.communityname, ct.hierarchy, ct.level, b.lastname + ', ' + b.firstname, b.startdate, b.agentconfigurationid, p.value from customer(nolock) b join profile p on b.account=p.account and p.attribute = 'ClientVersion' join community c on b.offerid = c.communityid join communitytree ct on c.communityid= ct.communityid join accountprofile ap on b.account=ap.account join ( select account, ActDate, ROW_NUMBER() over (partition by account order by ActDate desc) as rownum from activity ) ay on b.account=ay.account and ay.rownum = 1 where b.status 'C' The ROW_NUMBER() will, for each account, return the rows descending by activity date. Then, your join filters out all but the ones where rownum is 1, as you just want the latest. If this is a very common query, it may also be a good idea to add an index to the activity table, including the account and activity date as columns. That way, you can get a covering index, which would improve performance for this query (though you'll have to see if the drag on insert and update operations is worth the cost of the index). I would recommend trying @ThomasRushton's solution as well as this one and see which performs better; different workloads may lead to different results.