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
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.
I am using SQL 2012/2014.