We help IT Professionals succeed at work.

Update a column with MIN date grouping by ID

ttist25
ttist25 used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Aggregated subquery man.  Assuming that MyId is the unique ID here..

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

Open in new window

Author

Commented:
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