Creating a SQL Command to find MAX of Group

Hi Folks.  I am creating a SQL Command to query a incident management table.  I want to return incidents related to EVENTS which is a field on the incident form.  The problem is that there may have been 2 or 3 incidents created for the same event and in this case I want to only return the incident with the MAX Serverity.  My Command is as follows:
 
SELECT "T955"."Incident ID","T955"."Severity","T955"."EventID"
FROM   "ARSystem"."dbo"."T955"
WHERE "T955"."Severity"=(select MAX("T955"."Severity") from  "ARSystem"."dbo"."T955" "T955");

The problem with this is only when a Severity is set to '5' (the highest) does it return a value.
Thanks
Steve
sioccoAsked:
Who is Participating?
 
ErnariashConnect With a Mentor Commented:
Same problem, the alias is missing...:)
 MAX("T955"."C536871231")  AS "C536871231"


SELECT "T955"."C1000000161",
      "T955"."C536871231",
      "T955"."C536871225" 
FROM "ARSystem"."dbo"."T955" 
WHERE EXISTS 
(      Select 1 
      From (      select "T955"."C536871225" , MAX("T955"."C536871231")  AS "C536871231"
                  FROM "ARSystem"."dbo"."T955" 
                  Group by "T955"."C536871225"
            ) AS "T"      
WHERE "T955"."C536871225" = "T"."C536871225" And "T955"."C536871231" = "T"."C536871231" 
);

Open in new window

0
 
ErnariashCommented:
Try this:
SELECT "T955"."Incident ID","T955"."Severity","T955"."EventID"
FROM "ARSystem"."dbo"."T955"
WHERE "T955"."Severity"=(select MAX("T955"."Severity") from "ARSystem"."dbo"."T955" "T"
where "T955"."EventID" = "T"."EventID"
);
SELECT T955.Incident ID,T955.Severity,T955.EventID 
FROM ARSystem.dbo.T955 
WHERE T955.Severity=(select MAX(T955.Severity) from ARSystem.dbo.T955 T 
where T955.EventID = T.EventID 
);

Open in new window

0
 
sioccoAuthor Commented:
This is the results
Incident 123    Severity 4    Event ID 8734
Incident 456    Severity 1    Event ID 8734

I expect from the SQL Query only the following:

Incident 123   Severity 4   Event ID 8734

Thanks

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
sioccoAuthor Commented:
I tried your suggestion but it only gives me records back where the Priority is 5 (highest).  It will not give me back the 3's, 4's 1's etc.  I assume this has to fo with grouping?
Thanks
 
0
 
sioccoAuthor Commented:
To elaborate on my expected results:

Incident 123    Severity 4    Event ID 8734
Incident 456    Severity 1    Event ID 8734
Incident 222    Severity 2    Event ID 9888

I expect from the SQL Query only the following:

Incident 123   Severity 4   Event ID 8734
Incident 222    Severity 2    Event ID 9888
0
 
Terry WoodsIT GuruCommented:
Try this, expanding on Ernariash's suggestion
SELECT T955.Incident ID,T955.Severity,T955.EventID 
FROM ARSystem.dbo.T955 
WHERE T955.Severity=(select MAX(T.Severity) from ARSystem.dbo.T955 T 
where T955.EventID = T.EventID 
);
 
or this, if you have 2 incidents with the same (max) severity, and only want one:
 
SELECT max(T955.Incident ID), T955.Severity, T955.EventID 
FROM ARSystem.dbo.T955 
WHERE T955.Severity=(select MAX(T.Severity) from ARSystem.dbo.T955 T 
where T955.EventID = T.EventID 
)
group by Severity, EventID;

Open in new window

0
 
ZberteocCommented:
What happens if you have same severuty for same event in 2 different incidents:

Incident 123    Severity 4    Event ID 8734
Incident 234    Severity 4    Event ID 8734
Incident 456    Severity 1    Event ID 8734
Incident 222    Severity 2    Event ID 9888

0
 
ZberteocCommented:
This might work:
SELECT 
	T955.[Incident ID],
	T955.Severity,
	T955.EventID
FROM   
	ARSystem.dbo.T955 t
	INNER JOIN 
	(
		SELECT 
			min(T955.[Incident ID])	as [Incident ID],
			MAX(T955.Severity)		as MaxSeverity,
			T955.EventID
		FROM   
			ARSystem.dbo.T955
		GROUP BY
			T955.EventID
	) m
		ON m.EventID=t.EventID
		and m.MaxSeverity=t.Severity
		AND m.[Incident ID]=t.[Incident ID]
ORDER BY
	T955.EventID

Open in new window

0
 
ErnariashCommented:
Try this:

SELECT T955.Incident ID,T955.Severity,T955.EventID
FROM ARSystem.dbo.T955
WHERE EXISTS
( Select 1
From ( select T955.EventID, MAX(T955.Severity) Severity
from ARSystem.dbo.T955
Group by T955.EventID
) T
where T955.EventID = T.EventID And T955.Severity = T.Severity
);

SELECT T955.Incident ID,T955.Severity,T955.EventID 
FROM ARSystem.dbo.T955 
WHERE EXISTS 
(	Select 1 
	From (	select T955.EventID, MAX(T955.Severity)  Severity
					from ARSystem.dbo.T955
					Group by T955.EventID
		)	 T 
where T955.EventID = T.EventID And T955.Severity = T.Severity 
);

Open in new window

0
 
ZberteocConnect With a Mentor Commented:
Sorry, the first one I gave was wrong, try this:
SELECT 
	min(m.[Incident ID]) as [Incident ID],
	max(m.Severity)		 as MaxSeverity,
	m.EventID
FROM
	(
		SELECT 
			T955.[Incident ID],
			T955.Severity,
			T955.EventID
		FROM   
			ARSystem.dbo.T955 t
			INNER JOIN 
			(
				SELECT 
					min(T955.[Incident ID])	as [Incident ID],
					T955.Severity			as Severity,
					T955.EventID
				FROM   
					ARSystem.dbo.T955
				GROUP BY
					T955.EventID,
					T955.Severity
			) g
				ON g.EventID		=t.EventID
				and g.Severity		=t.Severity
				AND g.[Incident ID]	=t.[Incident ID]
	) m
GROUP BY
	m.EventID
ORDER BY
	m.EventID

Open in new window

0
 
sioccoAuthor Commented:
thanks everyone for their replies.  I have not had a chance to test this and will do so when back in the office tomorrow morning.  To answer your question ZBERTEOC just pick one of the 2 entries.
What happens if you have same severuty for same event in 2 different incidents:

Incident 123    Severity 4    Event ID 8734 pick this one
Incident 234    Severity 4    Event ID 8734
Incident 456    Severity 1    Event ID 8734
Incident 222    Severity 2    Event ID 9888

0
 
sioccoAuthor Commented:
Hmm, I couldn't get any of the queries to run properly without some errors. Here is what I have been able to run so far with sucess however the only thing I cannot do is return the Incident ID with the query results.  When I add it into the select in the first line it returns an error like the following:

"Column ARSystem.dbo.T955.INCIDENT ID is invalid in the select list because it is not contained in either an aggregate function of the GROUP BY Clause."  Problem is when I add a group BY INCIDENT ID the results are not consistent.

THIS WORKS:
SELECT  "T955"."C536871225",,  MAX("T955"."C536871231")
FROM "ARSystem"."dbo"."T955"
where "T955"."C536871225"="T955"."C536871225"
GROUP BY  "T955"."C536871225";

THIS DOES NOT
SELECT  "T955"."C536871225", MAX("T955"."C536871231"),"T955"."INCIDENT ID"
FROM "ARSystem"."dbo"."T955"
where "T955"."C536871225"="T955"."C536871225"
GROUP BY  "T955"."C536871225","T955"."INCIDENT ID" ;


Please help, thanks.
Steve
0
 
sioccoAuthor Commented:
TERRY AT OPUS, I tried your query and it works for the most part except it is not returning situations where there are 2+ incidents with the same severity.  See my QUERY:

SELECT max("T955"."C1000000161"),"T955"."C536871231", "T955"."C536871225"
FROM "ARSystem"."dbo"."T955"
WHERE "T955"."C536871231"=(select MAX("T955"."C536871231") from "ARSystem"."dbo"."T955"  
where  "T955"."C536871225"  =  "T955"."C536871225"  
)
group by "T955"."C536871231",  "T955"."C536871225" ;
0
 
ZberteocCommented:
Try my query. I took care of that.
0
 
ZberteocCommented:
The last one of course.
0
 
ErnariashCommented:
Please I do not have the data for testing this query but if you need 2+ incidents with the same severity. This should work:
This query will give you all insidents for the events with the max serverity.

SELECT T955.Incident ID,
	T955.Severity,
	T955.EventID 
FROM ARSystem.dbo.T955 
WHERE EXISTS 
(	Select 1 
	From (	select T955.EventID, MAX(T955.Severity)  Severity
			from ARSystem.dbo.T955
			Group by T955.EventID
		)	 T 
where T955.EventID = T.EventID And T955.Severity = T.Severity 
);

Open in new window

0
 
sioccoAuthor Commented:
Ernariash; trying to run your query as follows and get the following error:  
Incorrect Syntax near the Keyword WHERE (2nd where)

SELECT "T955"."C1000000161",
      "T955"."C536871231",
      "T955"."C536871225"
FROM "ARSystem"."dbo"."T955"
WHERE EXISTS
(      Select 1
      From (      select "T955"."C536871225" , MAX("T955"."C536871231")  
                  FROM "ARSystem"."dbo"."T955"
                  Group by "T955"."C536871225"
            )       
WHERE"T955"."C536871225" = "T955"."C536871225" And "T955"."C536871231" = "T955"."C536871231"
);
 


ZBERTEOC, I am trying your 2nd query.  I am not sure what you are doing with the m., g. and t's?
0
 
ErnariashCommented:
Hello Siocco:
Your will need to use an alias for the subquery.
Thanks, Ernesto

SELECT "T955"."C1000000161",
      "T955"."C536871231",
      "T955"."C536871225" 
FROM "ARSystem"."dbo"."T955" 
WHERE EXISTS 
(      Select 1 
      From (      select "T955"."C536871225" , MAX("T955"."C536871231")  
                  FROM "ARSystem"."dbo"."T955" 
                  Group by "T955"."C536871225"
            ) AS "T"      
WHERE "T955"."C536871225" = "T"."C536871225" And "T"."C536871231" = "T955"."C536871231" 
);

Open in new window

0
 
sioccoAuthor Commented:
Might be getting closer but now I get the following error:
No Column was specified for Column 2 of 'T'
0
 
sioccoAuthor Commented:
Zberteoc:
Can you elaborate on your syntax?  I am having trouble getting it to work for me.
SELECT
      min(m.[Incident ID]) as [Incident ID],
      max(m.Severity)             as MaxSeverity,
      m.EventID
FROM
      (
            SELECT
                  T955.[Incident ID],
                  T955.Severity,
                  T955.EventID
            FROM  
                  ARSystem.dbo.T955 t
                  INNER JOIN
                  (
                        SELECT
                              min(T955.[Incident ID])      as [Incident ID],
                              T955.Severity                  as Severity,
                              T955.EventID
                        FROM  
                              ARSystem.dbo.T955
                        GROUP BY
                              T955.EventID,
                              T955.Severity
                  ) g
                        ON g.EventID            =t.EventID
                        and g.Severity            =t.Severity
                        AND g.[Incident ID]      =t.[Incident ID]
      ) m
GROUP BY
      m.EventID
ORDER BY
      m.EventID
 
Open in New Window Select All
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.