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]
CREATE TABLE [dbo].[test](
id [int] NOT NULL,
[fk] [int] NOT NULL,
[col1] [int] NULL,
[col2] [int] NULL,
[col3] [int] NULL,
[changed] [datetime] NOT NULL,
[userid] [int] NOT NULL
ALTER TABLE dbo.test ADD CONSTRAINT pk_test PRIMARY KEY(id)
INSERT INTO test VALUES (1,1,1,NULL,3,GETDATE()+1,1)
INSERT INTO test VALUES (2,1,NULL,2,NULL,GETDATE()+2,2)
INSERT INTO test VALUES (3,1,4,NULL,NULL,GETDATE()+3,3)
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!