Selecting last modified record(s) from log table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting last modified record(s) from log table

Author

Topic

hephie
Starting Member

15 Posts

Posted - 2009-08-20 : 11:59:56

Hi,

I have a log table (called TB_Log).
Each time an object (eg. Person) is changed (inserted/update/delete), a record is created in the TB_Log.

The TB_Log exists out of 4 columns.
ObjectType nvarchar (eg. Person)
ObjectID int (eg. 1)
ChangeDate datetime (eg. 20/08/2009)
ActionBy nvarchar (eg. Thomas)

I want a select statement that selects all information (objecttype, objectid, changedate, actionby) for only the last modificiation (per objecttype/objectid).
So 1 row per objecttype/objectid with a changedate and a actionby.

The query i'm currently using is:


SELECT    
	[TB_Log].[ObjectType],	
	[TB_Log].[ObjectID], 
	MAX([TB_Log].[DateChanged]) AS LastActivityDate, 
	[TB_Log].[ActionBy] AS LastActivityBy,
	
FROM         
	[TB_Log]
				
GROUP BY
        [TB_Log].[ObjectType],	
	[TB_Log].[ObjectID], 
	[TB_Log].[ActionBy],

But with this query when you have 2 updates for the same objecttype and objectid by two different persons, you will have double information. It's logic as you group on the "ActionBy"-field.

The query will need to be modified, but i don't know how.
Hopefully some of you experts can help me! thanks in advance for the one who does!!

Thomas

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-08-20 : 12:27:38

[code]
SELECT
ObjectType
,ObjectID
,DateChanged AS LastActivityDate
,ActionBy AS LastActivityBy
FROM
(
SELECT
ObjectType
,ObjectID
,DateChanged
,ActionBy
,ROW_NUMBER() OVER (PARTITION BY ObjectType, ObjectID ORDER BY DateChanged DESC) AS RowNum
FROM
TB_Log
) D
WHERE RowNum = 1
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-20 : 12:27:59

You can do that by using row_number() function (I will show you).
But if there are too many entries in that logtable this solution will be slow.
Second way is to select the data with a subselect to get the max(date) for the particular record.

So how many rows are in that table?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-20 : 12:29:16