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!

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ttist25

ASKER

Thanks Jim!

I mad some small updates but that worked great!

UPDATE t
SET t.MyMinDateTime = t2.min_date
FROM #tbl_WithsomeFields t
  JOIN (
	SELECT MyId, MIN(MyDate) as min_date
	FROM #tbl_WithSomeFields
	GROUP BY MyId) t2 ON t.MyID = t2.MyID

Open in new window