Unsure how to structure a query to get what I want

[SpyderMan1234]

I have a table that has information that looks like the following:

CaseID Crime CrimeTime
------ ----- ---------
1 Aggravated Assault 01/01/01 12:00 PM
2 First Degree Murder 01/15/02 12:00 PM
2 Aggravated Assault 01/15/02 12:05 PM
2 Vehicular Homicide 01/16/02 12:10 PM
3 Contr. Del. of a Minor 01/16/02 10:34 PM
3 DUI 01/16/02 10:40 PM

...

I have a query that does an ORDER BY CaseID, CrimeTime so that all of the CaseIDs are in order and, within them, the times go in order from earliest to latest. Simple enough...
What I need is a query that will give me just the first instance of each CaseID (in other words, the first crime that was logged for each case). So that my results look like this:

CaseID Crime CrimeTime
------ ----- ---------
1 Aggravated Assault 01/01/01 12:00 PM
2 First Degree Murder 01/15/02 12:00 PM
3 Contr. Del. of a Minor 01/16/02 10:34 PM

Any ideas? Any help is greatly appreciated!

THANKS!

 

[lespaul]

SELECT CaseID, Crime, Min(CrimeTime) FROM TableName GROUP BY CaseID, Crime

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

SELECT A.CaseID, A.Crime, A.CrimeTime
FROM yourTable AS A INNER JOIN (
SELECT CaseID, Min(CrimeTime) AS FirstCrime FROM yourTable GROUP BY CaseID
) AS F ON A.CaseID = F.CaseID AND A.CrimeTime = F.FirstCrime

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

 

[SpyderMan1234]

Thanks for the quick reply. I've actually tried this one myself and it just returns the orginal, full recordset. It does not JUST return the first, logged instance of each CaseID like I'm wanting it to do.

I'll keep looking.

Thanks

 

[SpyderMan1234]

PH

When I try this:

SELECT A.CaseID, A.Crime, A.CrimeTime
FROM yourTable AS A INNER JOIN (
SELECT CaseID, Min(CrimeTime) AS FirstCrime FROM yourTable GROUP BY CaseID
) AS F ON A.CaseID = F.CaseID AND A.CrimeTime = F.FirstCrime

I get an error stating "Syntax error in from clause." I am using Access 97, if that makes a difference.

Thanks

 

[PHV]

Access 97, if that makes a difference
Big diff.
Create a query named, say, qryFirstCrime:
SELECT CaseID, Min(CrimeTime) AS FirstCrime
FROM yourTable
GROUP BY CaseID

Now, your query:
SELECT A.CaseID, A.Crime, A.CrimeTime
FROM yourTable AS A INNER JOIN qryFirstCrime AS F ON A.CaseID = F.CaseID AND A.CrimeTime = F.FirstCrime

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

 

[SpyderMan1234]

Excellent. This works perfectly. Thank you very much!

JH