SQL Query - Select MAX COUNT

Hi,

I am trying to use a SQL Query which will do a Select Distinct, Count(max) type statement.
Current Standard Query is below

SELECT        TOP (100) PERCENT dbo.Timesheets.evets_CaseId, dbo.Timesheets.evets_code, dbo.Projects.evepr__title
FROM            dbo.Timesheets LEFT OUTER JOIN
                         dbo.Projects ON dbo.Timesheets.evets_code = dbo.Projects.evepr__ProjectsID
WHERE        (dbo.Timesheets.evets_CaseId IS NOT NULL)
ORDER BY dbo.Timesheets.evets_CaseId

Select allOpen in new window

I have attached a picture on how a table looks


then how I would like it to be displayed.


The idea is to use SELECT Distinct on the Case_CaseID, Get the count of (evets_code) and get the MAX number which will then show the evepr_title column (Can do this in a separate query/view if required).

Not sure how I can do this, I have looked at many different group by and sub queries but still struggling.

Any ideas?

I am using SQL 2012/2014.

Thanks,