troubleshooting Question

Update a column with MIN date grouping by ID

Avatar of ttist25
ttist25 asked on
Microsoft SQL Server
2 Comments1 Solution63 ViewsLast Modified:
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', '');

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
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros