Avatar of ttist25
ttist25

asked on 

Update a column with MIN date grouping by ID

Hey guys,

I have a table with some fields:

DROP TABLE #tbl_WithSomeFields 
CREATE TABLE
#tbl_WithSomeFields (
		 MyID VARCHAR(255)
		,MyPID VARCHAR(20)
		,MyOtherThing VARCHAR(100)
		,MyOtherOtherThing VARCHAR(10)
		,MyDate DATETIME
		,MyMinDateTime DATETIME		
);

INSERT INTO 
#tbl_WithSomeFields (MyID, MyPID, MyOtherThing, MyOtherOtherThing, MyDate, MyMinDateTime) 
VALUES 
    	('1', '1234567', 'A', '12C', '2018-10-24 20:39:00', ''),
        ('1', '1234567', 'A', '11C', '2018-10-24 15:39:00', ''),
        ('2', '1234567', 'A', '12B', '2018-10-23 20:39:00', ''),
        ('2', '1234567', 'A', '13D', '2018-10-24 20:39:00', ''),
        ('3', '1234567', 'A', '8', '2019-10-24 20:39:00', '');

Open in new window


I would like to UPDATE MyMinDateTime with the MIN(MyDate) value grouping by MyID.

The expected result would be:
For MyID=1 MyMinDateTime=2018-10-24 15:39:00.000
For MyID=2 MyMinDateTime=2018-10-23 20:39:00.000
For MyID=3 MyMinDateTime=2019-10-24 20:39:00.000

Can't seem to figure out the best way to do this.  Any help will be greatly appreciated.

Thanks!
Microsoft SQL Server

Avatar of undefined
Last Comment
ttist25

8/22/2022 - Mon