Link to home
Start Free TrialLog in
Avatar of BoltonWanderer

asked on

SQL Query - Select MAX COUNT


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

Open in new window

I have attached a picture on how a table looks
User generated image

then how I would like it to be displayed.

User generated image
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.

Avatar of BoltonWanderer


Hi Jim, Updated as requested. Thanks,
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Sharath,

Thanks for the reply. That has worked perfectly.