sql - MSSQL 2008: Get last updated record by specific field -

sql - MSSQL 2008: Get last updated record by specific field -

i'm having simplified table called content in mssql:

contentid updatedat fileid 1 2014-01-01 00:00:00 file-a 1 2014-02-02 00:00:00 file-b 1 2014-03-03 00:00:00 file-b 2 2012-12-30 00:00:00 file-x 2 2012-12-31 00:00:00 file-y

what want accomplish following:

get each row table content, fileid has been updated compared it's previous version. result should following:

contentid updatedat fileid 1 2014-02-02 00:00:00 file-b 2 2012-12-31 00:00:00 file-y

what have tried far:

looked different solutions , found lag() function, seemed promising. feature seems available in mssql 2012, error "the parallel info warehouse (pdw) features not enabled" states. tried wrap head around cursors, since i'm noob this, couldn't find running solution. lots of nested queries, ugh... since new row created each update, assume nested queries not way go because of performance-reasons.

in sql server 2012, utilize lag(). can replicate in various ways in sql server 2008. here method using cross apply:

select c.* content c cross apply (select top 1 c2.* content c2 c2.contentid = c.contentid , c2.updatedat < c.updatedat order c2.updatedat desc ) cprev c.fileid <> cprev.fileid;

sql sql-server sql-server-2008