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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
ZberteocCommented:
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
ErnariashCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.