Avatar of BoltonWanderer
BoltonWanderer
 asked on

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

Open in new window


I have attached a picture on how a table looks
Table

then how I would like it to be displayed.

Ideal
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,
Microsoft SQL Server

Avatar of undefined
Last Comment
BoltonWanderer

8/22/2022 - Mon
BoltonWanderer

ASKER
Hi Jim, Updated as requested. Thanks,
ASKER CERTIFIED SOLUTION
Sharath S

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
BoltonWanderer

ASKER
Hi Sharath,

Thanks for the reply. That has worked perfectly.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck