SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table table1 (id int, name varchar(100), time date);
insert into table1 values (1,'Hans',GETDATE()-10);
insert into table1 values (1,'Hans',GETDATE()-5);
insert into table1 values (1,'Hans',GETDATE()-2);
3 rows affected

insert into table1 values (2,'Anna',GETDATE()-1);
insert into table1 values (2,'Anna',GETDATE()-7);
insert into table1 values (2,'Anna',GETDATE()-8);
3 rows affected

select id, name, time from table1 order by id, time;


id
name
time


1
Hans
2023-07-10

1
Hans
2023-07-15

1
Hans
2023-07-18

2
Anna
2023-07-12

2
Anna
2023-07-13

2
Anna
2023-07-19


WITH earliest AS
  (SELECT id, MIN(time) AS eTime
  FROM table1 
    GROUP BY id)
DELETE FROM table1 
  WHERE NOT EXISTS 
    (SELECT 1 FROM earliest WHERE id = table1.id AND table1.time = eTime); 
4 rows affected

select id, name, time from table1 order by id, time;


id
name
time


1
Hans
2023-07-10

2
Anna
2023-07-12

id	name	time
1	Hans	2023-07-10
1	Hans	2023-07-15
1	Hans	2023-07-18
2	Anna	2023-07-12
2	Anna	2023-07-13
2	Anna	2023-07-19