Reputation: 1211
I have a log table, each row representing an object logging its state. Each object has a unique, unchanging GUID. There are multiple objects logging their states, so there will be thousands of entries, with objects continually inserting new logs. Everytime an object checks in, it is via an INSERT.
I have the PrimaryKey, GUID, ObjectState, and LogDate columns in tblObjects. I want to select the latest (by datetime) log entry for each unique GUID from tblObjects, in effect a 'snapshot' of all the objects.
How can this be accomplished?
Upvotes: 1
Views: 1693
Reputation: 11804
It sounds like you want to select the most recent LogDate for each individual GUID. Using a "group by" with the max function should do what you need:
Select *, max(LogDate)
From tblObjects
Group By GUID
Upvotes: 0
Reputation: 238048
You could use a subquery to filter for the last log entries:
select t1.*
from tblObjects t1
where t1.LogDate = (
select max(LogDate)
from tblObjects t2
where t1.guid = t2.guid
)
Or alternatively, using not exists:
select t1.*
from tblObjects t1
where not exists (
select *
from tblObjects t2
where t1.guid = t2.guid
and t1.LogDate < t2.LogDate
)
Note that the usual approach would be to store a bitflag indicating whether a row is current. That allows you to query faster.
Upvotes: 3