I want to add a new column containing the Min date associated with each ID, data looks like this:

I want my data to look like this:

I've tried the below but no luck, I want to add the column onto my existing table so want to avoid a select function.

ALTER TABLE dbo.mytable
ADD First_date AS   MIN(Datee)
GROUP BY ID

CodePudding user response：

UPDATE XY SET First_date = Y.FDate 
FROM [TABLE_NAME] XY 
 JOIN (SELECT MIN(Datee) as FDate,ID FROM [TABLE_NAME] group by ID) Y
 ON XY.ID=Y.ID

This will cater your requirement. Let me know if you have any doubt.

CodePudding user response：

As I stated in the question's comments a computed column cannot use an aggregate function, as a computed column can only reference the values of columns in the row it is in. Instead use a VIEW:

CREATE VIEW dbo.YourView AS
    
    SELECT ID,
           Datee,
           MIN(Datee) OVER (PARTITION BY ID) AS FirstDate
    FROM dbo.MyTable;
GO

Then you can reference that VIEW instead:

SELECT ID,
       Datee,
       FirstDate
FROM dbo.MyView;

Though you could create a column and UPDATE all the rows, as soon as your data changes the column would be out of date; thus you would need to ensure that the needed rows were UPDATEd every time a DML statement is run against your table.

There is also the option of using a scalar function, which would allow you to use a computed column, however, it would have to be an inline scalar function, and such functions are often slow. Thus using a VIEW is more than likely going to be the most performant option.

CodePudding user response：

As mentioned by Larnu, you shouldn't be doing this. But if you actually want to, you could create the Column [First_date] and a trigger to update the table after every insert.

UPDATE [TABLE_NAME] SET First_date = (SELECT MIN(Datee) FROM [TABLE_NAME] GROUP BY ID)