Mapping to userid who last updated record

Transact-SQL
1

Ive a got a CTE and grouping by last updated date to get last record updated, however I have to remove the 'Updated By' & 'updated by name' column from SELECT & GROUP BY to get what I want.

This is due to cases where a record has been updated at very close times by different users but nothing evident has been changed, so then it gives me another record with the new updated date and the updated by user, who is generally a different person.

If I do as below then I will get the grouping of max user date and user name, but if I want to map the max updated to the Updated By user, then is there a way to do that so that it shows the max updated date not taking the user in to consideration, but somehow map the data to the user who done the last updated by?

with cte AS(
	
	 SELECT 
     a.CusID    
	,a.[UpdatedBy]
	,a.[Updated By Name]
	,max(a.[UpdatedDate]) [Max-Updated-By]
	,a.[ID]
	,a.[Activity Number]
	FROM TABLE a 
	WHERE a.date between '20191031' and getdate()
	group by 
     a.CusID
	,a.[UpdatedBy]
	,a.[Updated By Name]
    ,a.[ID]
	,a.[Activity Number]
)

	--

	select 
	cte.CusID    
	,cte.[UpdatedBy]
	,cte.[Updated By Name]
	,max(cte.[UpdatedDate]) [Max-Updated-By]
	,cte.[ID]
	,cte.[Activity Number]
	from cte


CusID    [type12_UpdatedBy]      [Updated By Name]      [Max-Updated-By]      [ID]      [Activity Number]
=====      =================      ================      =================      ====      ================

1001      SmithJa                 James Smith           01012020 13:00         5          22
1001      JonesCr                 Craig Jones           01012020 13:06         5          22

If I comment out the updated by user and their updated by name it gives me what I want i.e. only the most recently updated, but I want to see the name of the user who done it.

with cte AS(
	
	 SELECT 
     a.CusID    
	--,a.[UpdatedBy]
	--,a.[Updated By Name]
	,max(a.[UpdatedDate]) [Max-Updated-By]
	,a.[ID]
	,a.[Activity Number]
	FROM TABLE a 
	WHERE a.date between '20191031' and getdate()
	group by 
     a.CusID
	--,a.[UpdatedBy]
	--,a.[Updated By Name]
    ,a.[ID]
	,a.[Activity Number]
)

	--

	select 
	cte.CusID    
	--,cte.[type12_UpdatedBy]
	--,cte.[Updated By Name]
	,max(cte.[type12_UpdatedDate]) [Max-Updated-By]
	,cte.[ID]
	,cte.[Activity Number]
	from cte


CusID    [type12_UpdatedBy]      [ID]      [Activity Number]
=====      =================     ====      ================
1001        01012020 13:06         5          22

Probably something simple but I can't get it out of my head what it is.

Thanks

Andrew

2 
;WITH cte AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY [Activity Number] ORDER BY UpdatedDate DESC) AS row_num
    FROM dbo.[TABLE] 
	WHERE date BETWEEN '20191031' AND GETDATE()
)
SELECT 
     a.CusID
	,a.[UpdatedBy]
	,a.[Updated By Name] AS [Last_Updated_By]
	,a.[UpdatedDate] AS [Last_Updated_Date]
	,a.[ID]
	,a.[Activity Number]
FROM cte c
WHERE row_num = 1
1 Like
3

Thanks Scot, hopefully this should now work. Thanks very much!