Questions about Group By

[studentcp]

By using Group By, I only know how to do for two columns Such as:
SELECT Min(Incident_Event.Date_Time) AS ArriveTime, Response.Incidentid

FROM Response INNER JOIN Incident_Event ON Response.ResponderId = Incident_Event.ResponderId

WHERE (((Incident_Event.EventEnum)=100005) AND ((Incident_Event.Date_Time)>#1/1/1800#))
GROUP BY Response.Incidentid;


Here is my question:
If I want the query returns more than two columns, what should I change the code. For example:

"SELECT Min(Incident_Event.Date_Time) AS ArriveTime, Response.Incidentid, Response.Responder_Type,Response.Responder_ID"

Thanks for your time.

 

[AlexCuse]

Any columns you add, you will just need to add to your group by as well.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[studentcp]

If I add it to my group by, the result will be:

ArriveTime Responder_Type Incidentid
2006-01-23 7:36:00 AM AS 1
2006-01-23 7:37:10 AM BS 1
2006-01-03 2:25:19 PM BS 2
2006-01-03 1:16:45 PM BS 7
2006-01-03 1:17:04 PM AS 7
2006-01-03 1:14:00 PM FR 7


but the result what I want should be:
ArriveTime Responder_Type Incidentid
2006-01-23 7:36:00 AM AS 1

2006-01-03 2:25:19 PM BS 2

2006-01-03 1:14:00 PM FR 7

only the earliest time will be left for each Incidentid
and the Responder_Type as well

 

[Golom]

SELECT E.Date_Time AS ArriveTime, Responder_Type, R.Incidentid

FROM Response R INNER JOIN Incident_Event E
  ON R.ResponderId = E.ResponderId

WHERE E.EventEnum=100005 
  AND E.Date_Time = (Select MIN(Date_Time) From Incident_Event X
                     Where X.ResponderId = R.ResponderId 
                       AND X.Date_Time>#1/1/1800#)

 

[studentcp]

Hi Golom,
I tired the code,it doesnot work.
Thank you very much.

 

[r937]

does not work? what happened?

did the server crash? did the query run at all, or did it get a syntax error? if so, what was the syntax error? or perhaps the query ran, but never finished? or ran, but returned no rows? or ran but returned the wrong rows?

r937.com | rudy.ca

 

[studentcp]

it ran, but a longer time, and the result is not right.

 

[PHV]

Perhaps this ?
SELECT E.Date_Time AS ArriveTime, Responder_Type, R.Incidentid
FROM Response R INNER JOIN Incident_Event E
ON R.ResponderId = E.ResponderId
WHERE E.EventEnum=100005
AND E.Date_Time = (Select MIN(Date_Time) From Incident_Event X
Where X.ResponderId = R.ResponderId
And X.Date_Time>#1/1/1800# [!]And X.EventEnum=100005[/!])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[studentcp]

HI PHV,
Thank you very much. The result doesnot look like what I want and it seems the qry never stop.

The part of result from that code:
ArriveTime Responder_Type Incidentid
2006-01-03 7:02:22 PM BLS 416
2006-01-03 7:17:42 PM ALS 416
2006-01-05 8:35:13 AM BLS 21
2006-01-07 9:45:29 AM ALS 24
2006-01-07 9:45:42 AM BLS 24
2006-01-07 6:09:59 AM BLS 26


the one I want:

ArriveTime Responder_Type Incidentid
2006-01-03 7:02:22 PM BLS 416

2006-01-05 8:35:13 AM BLS 21
2006-01-07 9:45:29 AM ALS 24

Thanks every one.

2006-01-07 6:09:59 AM BLS 26

 

[PHV]

What is your actual code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[studentcp]

Only want to group the Response.Incidentid, and return Response.Responder_Type as well.
Here is the CODEIT DOESNOT WROK, if I group Response.Responder_Type as well, the result is not right)
SELECT Min(Incident_Event.Date_Time) AS ArriveTime, Response.Incidentid, Response.Responder_Type

FROM Response INNER JOIN Incident_Event ON Response.ResponderId = Incident_Event.ResponderId

WHERE (((Incident_Event.EventEnum)=100005) AND ((Incident_Event.Date_Time)>#1/1/1800#))
GROUP BY Response.Incidentid;

 

[PHV]

I asked for the code giving the results you've posted 13 Feb 07 16:14.

 

[studentcp]

sorry, you mean this one
ArriveTime Responder_Type Incidentid
2006-01-03 7:02:22 PM BLS 416

2006-01-05 8:35:13 AM BLS 21
2006-01-07 9:45:29 AM ALS 24

2006-01-07 6:09:59 AM BLS 26

I made it by hand which the one I want.