Posted by Erland Sommarskog on 08/13/05 13:26

(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/techinfo/productdoc/2000/books.asp

[Back to original message]