Last Entry in a Log

(I don't post here often, so in case I'm violating long-standing taboos
of this newsgroup, I apologize in advance for calling a relation a
table, using nulls, and other ignorant, destructive, and comtemptible
terminology.)

I have a table that's keeping a sort of running log of different types
of changes to pieces of data. The table has a foreign key of the data
being changed, the foreign key for the type of change occuring, some
information about the change in a couple more columns, and a timestamp
for each entry. So it's:

dataID
eventID
eventInfo
timestamp

What I'd like to do, if at all possible, is a single SQL query that,
given a dataID, returns the most recent eventInfo and timestamp for
each eventID. Is this possible?

Many thanks.
-Eric(eric404@.gmail.com) writes:
> I have a table that's keeping a sort of running log of different types
> of changes to pieces of data. The table has a foreign key of the data
> being changed, the foreign key for the type of change occuring, some
> information about the change in a couple more columns, and a timestamp
> for each entry. So it's:
> dataID
> eventID
> eventInfo
> timestamp
> What I'd like to do, if at all possible, is a single SQL query that,
> given a dataID, returns the most recent eventInfo and timestamp for
> each eventID. Is this possible?

SELECT a.eventID, a.eventInfo, a.timestamp
FROM tbl a
JOIN (SELECT eventID, timestamp = MAX(timestamp)
FROM tbl b
WHERE dataID = @.dataid)
GRUOP BY eventID) AS b ON a.eventID = b.eventID
AND a.timestamp = b.timestamp
WHERE a.dataID = @.dataid

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Is your "timestamp" column actually a DATETIME or SMALLDATETIME column? If
so, try the following query. Don't use the name "timestamp", which refers to
a different datatype in SQL Server. The TIMESTAMP datatype has nothing to do
with date and time so if your column is in fact a TIMESTAMP then you ought
to add a DATETIME column instead.

SELECT eventid, eventinfo, timestamp
FROM YourTable AS T
WHERE timestamp =
(SELECT MAX(timestamp)
FROM YourTable
WHERE eventid = T. eventid
AND dataid = @.dataid)
AND dataid = @.dataid ;

--
David Portas
SQL Server MVP
--|||Thanks for the help, it works great.