Link to home
Create AccountLog in
Avatar of smithmrk
smithmrkFlag for United States of America

asked on

SQL - Get Highest Count

This seems so Simple, yet I'm unable to figure it out!
I have a SubQuery that returns the number (Count) of comments made on an incident, I then want to get the Highest Number (Count) and put that in a column.

For example:
Comments     Highest Comment
1                     9
4                     9
9                     9
5                     9
6                     9

Here is what I have so far (this outputs all the fields plus the number of comments made...now I just need the max number of comments)
SELECT *,
            ISNULL((SELECT COUNT(IncidentID) FROM tbl_Incident_Comments IC WHERE IC.IncidentID = INS.IncidentID GROUP BY IC.IncidentID),0) AS 'CommentCount'
FROM  tbl_Incident_Numbers INS
WHERE IncidentDate BETWEEN @BeginDate AND @EndDate
ORDER BY INS.IncidentID
Avatar of NathanHart
NathanHart

The highest number of what? All comments? If so, try this:

SELECT
  i.IncidentID
, ISNULL(h.CommentCount, 0) AS 'CommentCount'
, (SELECT TOP 1 COUNT(IncidentID)
               FROM
                tbl_Incident_Comments 
               GROUP BY
                IncidentID
                ORDER BY COUNT(IncidentID) DESC)  AS 'HighestComment'
FROM
  tbl_Incident_Numbers	i
  INNER JOIN ( SELECT
                IncidentID
              , COUNT(c.IncidentID) AS 'CommentCount'
               FROM
                tbl_Incident_Comments c
               GROUP BY
                IncidentID ) AS h ON h.IncidentID = i.IncidentID
WHERE IncidentDate BETWEEN @BeginDate AND @EndDate
ORDER BY
  i.IncidentID

Open in new window

Avatar of Kevin Cross
You can use the MAX() aggregate with an OVER() analytic clause. I will work on a demonstration, but it is very clean.
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of smithmrk

ASKER

Thanks to both of you for helping me through this issue and as I mentioned I knew it should have been easy, but I just couldn't figure it out without both your help.  I went with what I felt worked the best for me and that was this line of code:

Note, the solution is simply MAX(COUNT(IncidentID)) OVER().

Thanks again for taking the time to help me through this issue!