SQL Server 2022

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

create table table1 (id int, yourdate date);
insert into table1 values (1,GETDATE());
insert into table1 values (1,GETDATE()-1);
insert into table1 values (3,GETDATE());
insert into table1 values (3,GETDATE()-1);
insert into table1 values (3,GETDATE()-2);
insert into table1 values (4,GETDATE());
insert into table1 values (4,GETDATE()-1);
insert into table1 values (5,GETDATE());
insert into table1 values (5,GETDATE()-1);
insert into table1 values (6,GETDATE());
insert into table1 values (6,GETDATE()-1);
insert into table1 values (6,GETDATE()-2);
insert into table1 values (7,GETDATE());
insert into table1 values (7,GETDATE()-1);
insert into table1 values (7,GETDATE()-2);
15 rows affected

SELECT * FROM table1;


id
yourdate


1
2023-07-21

1
2023-07-20

3
2023-07-21

3
2023-07-20

3
2023-07-19

4
2023-07-21

4
2023-07-20

5
2023-07-21

5
2023-07-20

6
2023-07-21

6
2023-07-20

6
2023-07-19

7
2023-07-21

7
2023-07-20

7
2023-07-19


WITH dates AS
(SELECT id, MIN(yourdate) AS minDate, MAX(yourdate) AS maxDate
FROM table1
GROUP BY id)
DELETE FROM table1
  WHERE 
    NOT EXISTS 
      (SELECT 1 FROM dates d 
        WHERE table1.id = d.id 
          AND table1.yourdate IN (d.minDate, d.maxDate));
3 rows affected

SELECT * FROM table1;


id
yourdate


1
2023-07-21

1
2023-07-20

3
2023-07-21

3
2023-07-19

4
2023-07-21

4
2023-07-20

5
2023-07-21

5
2023-07-20

6
2023-07-21

6
2023-07-19

7
2023-07-21

7
2023-07-19


WITH dates AS
(SELECT id, MIN(yourdate) AS minDate, MAX(yourdate) AS maxDate
FROM table1
GROUP BY id)
DELETE FROM table1
  WHERE 
    NOT EXISTS 
      (SELECT 1 FROM dates d 
        WHERE table1.id = d.id 
          AND table1.yourdate IN (d.minDate, d.maxDate));
SELECT * FROM table1;


id
yourdate


1
2023-07-21

1
2023-07-20

3
2023-07-21

3
2023-07-19

4
2023-07-21

4
2023-07-20

5
2023-07-21

5
2023-07-20

6
2023-07-21

6
2023-07-19

7
2023-07-21

7
2023-07-19

id	yourdate
1	2023-07-21
1	2023-07-20
3	2023-07-21
3	2023-07-20
3	2023-07-19
4	2023-07-21
4	2023-07-20
5	2023-07-21
5	2023-07-20
6	2023-07-21
6	2023-07-20
6	2023-07-19
7	2023-07-21
7	2023-07-20
7	2023-07-19