Select max update time for each ID

[ehenry]

I have a staging table with an update time column and an ID column. In the final table the ID is unique, but in this table there are multiple IDs with different update times. I need to select the Max Update time for each ID in the staging table to perform an update on the final table. What is the best way to do this?

 

[Qik3Coder]

Normally, depending on your version of SQL you can do this with a CTE or a subquery with ROW_Number.

You can probably just get away with a

Update tf
set tf.LastUpdate = ti.LastUpdate
from tableFinal tf 
JOIN 
[tab](Select ID, Max(LastUpdate) LastUpdate
[tab] FROM ableIntermediate ti_inner) as ti ON
tf.ID = ti.ID

This format should get you close

Lodlaiden

A lack of experience doesn't prevent you from doing a good job.

 

[ehenry]

SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY UPDATETIME DESC), *
FROM TBL

If I SELECT all with Row# = 1 that should be the most recent entry for that ID. Then perform update from here.