SQL Get Latest Unique Rows

Question

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?

Answer 1

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.

Answer 2

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