SQL Server 2022

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

CREATE TABLE t
(
    Id INT PRIMARY KEY NOT NULL,
    Weight INT NULL,
    Batch INT NULL,
    t_stamp datetime null
);


INSERT INTO t
VALUES (31, 3994, 2, '2023-12-05 06:19:58.990'),
        (32, 4052, 2, '2023-12-05 06:37:24.883'),
        (33, 4000, 5, '2023-12-05 08:44:09.780'),
        (34, 4058, 5, '2023-12-05 08:58:59.683'),
        (35, 4032, 8, '2023-12-05 11:18:11.727'),
        (36, 3983, 11, '2023-12-05 13:20:35.167'),
        (37, 4013, 11, '2023-12-05 13:33:54.877'),
        (38, 3993, 14, '2023-12-05 15:29:50.060'),
        (39, 3470, 14, '2023-12-05 15:48:51.053'),
        (40, 3996, 14, '2023-12-05 17:20:09.893'),
        (41, 3348, 14, '2023-12-05 17:39:22.477'),
        (42, 3993, 20, '2023-12-05 19:48:42.117'),
        (43, 4054, 20, '2023-12-05 20:09:12.217'),
        (44, 3991, 23, '2023-12-05 22:55:45.020'),
        (45, 4065, 23, '2023-12-05 23:16:12.077'),
        (46, 3993, 26, '2023-12-06 02:56:29.713'),
        (47, 4033, 26, '2023-12-06 03:12:24.483'),
        (48, 3982, 26, '2023-12-06 06:06:45.720'),
        (49, 4075, 26, '2023-12-06 06:35:05.197'),
        (50, 3994, 4, '2023-12-06 08:02:58.887'),
        (51, 3029, 4, '2023-12-06 08:28:21.260'),
        (52, 3982, 7, '2023-12-06 09:49:23.497'),
        (53, 4038, 7, '2023-12-06 10:12:41.707')
23 rows affected

delete from d
from (
	select *, Row_Number() over(partition by batch order by weight desc) rn
	from t
)d
where rn > 1;
13 rows affected

select * from t order by 1;


Id
Weight
Batch
t_stamp


32
4052
2
2023-12-05 06:37:24.883

34
4058
5
2023-12-05 08:58:59.683

35
4032
8
2023-12-05 11:18:11.727

37
4013
11
2023-12-05 13:33:54.877

40
3996
14
2023-12-05 17:20:09.893

43
4054
20
2023-12-05 20:09:12.217

45
4065
23
2023-12-05 23:16:12.077

49
4075
26
2023-12-06 06:35:05.197

50
3994
4
2023-12-06 08:02:58.887

53
4038
7
2023-12-06 10:12:41.707

Id	Weight	Batch	t_stamp
32	4052	2	2023-12-05 06:37:24.883
34	4058	5	2023-12-05 08:58:59.683
35	4032	8	2023-12-05 11:18:11.727
37	4013	11	2023-12-05 13:33:54.877
40	3996	14	2023-12-05 17:20:09.893
43	4054	20	2023-12-05 20:09:12.217
45	4065	23	2023-12-05 23:16:12.077
49	4075	26	2023-12-06 06:35:05.197
50	3994	4	2023-12-06 08:02:58.887
53	4038	7	2023-12-06 10:12:41.707