First real post here although an avid reader of some of the posts. Mainly a DBA but started to get more in the TSQL development side of things. Came across a problem that I'm struggling to get a good set-based solution to (I can do this with cursors, but I'd rather stay away from them if possible).

Basically, I am having to create a table that is the child of the parent table. It mirrors the structure of the parent table with an additional 3 columns, a PK, a date changed (inserted) and a user_id field.

The application will insert a row (which is essentially a change record to the parent) for every change to the parent record that a user makes. So if they change all 3 attributes, all the col1/2/3 are populated and so on.

I basically need to bring back the latest revisions to the attributes based on the date. I need to bring back the foreign key (fk) and cols 1, 2 and 3 to display to the user. See the code.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))DROP TABLE [dbo].[test]GO

So for this example, I would like a single row displayed with the values

1,4,2,3 for cols fk,col1,col2,col3

I have no requirement to display anything other than that - so effectively it'd be like collapsing the row to the latest date. I've looked a using ROLLUP, but I'm not sure it will give me what I need. Like I say, I could do this using cursors, but I'm hoping there's a better way. Anyone else had to do something like this? Any help would be greatly appreciated!

WITH CTE AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY fk ORDER BY CASE WHEN col1 IS NOT NULL THEN 0 ELSE 1 END, changed DESC) AS rn1, ROW_NUMBER() OVER(PARTITION BY fk ORDER BY CASE WHEN col2 IS NOT NULL THEN 0 ELSE 1 END, changed DESC) AS rn2, ROW_NUMBER() OVER(PARTITION BY fk ORDER BY CASE WHEN col3 IS NOT NULL THEN 0 ELSE 1 END, changed DESC) AS rn3FROM test)SELECT fk, MAX(CASE WHEN rn1=1 THEN col1 END) AS col1, MAX(CASE WHEN rn2=1 THEN col2 END) AS col2, MAX(CASE WHEN rn3=1 THEN col3 END) AS col3FROM CTEGROUP BY fk;