[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Creating a SQL Command to find MAX of Group

Posted on 2008-11-06
20
Medium Priority
?
407 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:siocco
  • 9
  • 5
  • 5
  • +1
20 Comments
 
LVL 9

Expert Comment

by:Ernariash
ID: 22899146
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
 

Author Comment

by:siocco
ID: 22899153
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
 

Author Comment

by:siocco
ID: 22899267
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:siocco
ID: 22899283
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 22899802
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22899874
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22899905
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 22900074
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
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 600 total points
ID: 22900782
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
 

Author Comment

by:siocco
ID: 22901372
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
 

Author Comment

by:siocco
ID: 22904756
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
 

Author Comment

by:siocco
ID: 22905107
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22905608
Try my query. I took care of that.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22905612
The last one of course.
0
 
LVL 9

Expert Comment

by:Ernariash
ID: 22905648
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
 

Author Comment

by:siocco
ID: 22906529
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 22906736
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
 

Author Comment

by:siocco
ID: 22906844
Might be getting closer but now I get the following error:
No Column was specified for Column 2 of 'T'
0
 
LVL 9

Accepted Solution

by:
Ernariash earned 1400 total points
ID: 22907006
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
 

Author Comment

by:siocco
ID: 23187474
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question