Selecting only the last record for each date?

[AnotherJob]

I have some data that has multiple records per day, and each record has a timestamp. What I want to do is to select only the last entry for each date.

I've already got code to do that, using a code loop, but I feel sure there must be some SQL statement what will do the selection for me. Can anyone here give an example please?

 

[randy700]

How about...

Max(YourDateField) or
Max(YourTimeField) WHERE YourDateField = SomeValue

Randy

 

[AnotherJob]

> How about...

Thanks, but those are just criteria expressions. What I am doing now is using a GROUP BY to separate my data by dates and then looping through, using MAX to choose the latest time within each date. But I feel sure there must be a way to reduce the whole mess to a SQL query that will return the full records, selecting only those that came last for each date.

 

[randy700]

It might help if you post the code you want to modify.

Randy

 

[AnotherJob]

> . . .the code you want to modify.

I'm not looking to modify my code, I'm looking for a SQL statement to replace my code. But let me give an example that may help illustrate my original post. Suppose I have data like the following:

DateTime_Field Several Fields of other data . . .
1/1/1970 10 AM aaa, bbb, ccc
1/1/1970 1 PM aaa, bbb, ccc
1/2/1970 9 AM aaa, bbb, ccc
1/2/1970 11 AM aaa, bbb, ccc
1/2/1970 12 PM aaa, bbb, ccc
3/3/1982 6 AM aaa, bbb, ccc
3/4/1982 7 AM aaa, bbb, ccc
3/4/1982 1 PM aaa, bbb, ccc

What I'm looking for is a SQL query that will select only the last record from each date, so:

DateTime_Field Several Fields of other data . . .
1/1/1970 1 PM aaa, bbb, ccc
1/2/1970 12 PM aaa, bbb, ccc
3/3/1982 6 AM aaa, bbb, ccc
3/4/1982 1 PM aaa, bbb, ccc

 

[lespaul]

It may be as easy as:

SELECT Several Fields of Other Data, Max(DateTime_Field)
FROM TableName
GROUP BY Several Fields of Other Data

it may be that you need to do something more like:

SELECT Several Fields of Other Data, LastDateTime
FROM tableName T
INNER JOIN (SELECT KeyField, Max(DateTime_Field) As LastDateTime FROM TableName GROUP BY KeyField) A ON T.KeyField = A.KeyField and T.DateTime_field = A.DateTime_field

HTH

Leslie

Have you met Hardy Heron?

 

[AnotherJob]

Your example #2 is what I came up with, but unfortunately the "T.DateTime_field = A.DateTime_field" join is not supported in Jet SQL. Likewise, "T.DateTime_field = LastDateTime" is not supported. I think it has something to do with the fact that the "T.DateTime_field" is not part of the GROUP BY criteria.

SELECT [T].[Date],[T].[Time],[T].[Glucose]
FROM [tblGlucoseData] AS [T]
INNER JOIN (SELECT [A].[Date], MAX([A].[Time]) FROM [tblGlucoseData] GROUP BY [A].[Date]) AS [A]
ON ([T].[Date] = [A].[Date]) AND ([T].[Time] = [A].[Time]);

 

[PHV]

SELECT T.Date,T.Time,T.Glucose
FROM tblGlucoseData AS T
INNER JOIN (SELECT [Date], MAX([Time]) AS MaxTime FROM tblGlucoseData GROUP BY [Date]) AS A
ON T.Date = A.Date AND T.Time = A.MaxTime

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AnotherJob]

Thanks, but that is the "likewise" that I mention as not being supported.

 

[PHV]

Do you really have TWO fields (Date and Time) to store a SINGLE DateTime value ?

 

[AnotherJob]

Yes indeed, the medical software that produces the data file uses separate fields for date and time. And why not? Even if I combined them into a single field I would need to separate the date using the DateValue() function in order to GROUP BY date.

 

[PHV]

not being supported
Which version of access ?
Which REAL SQL code ?