MySQL 8.0

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

CREATE TABLE disk
    (id int, b varchar(7), c varchar(7), d int, e varchar(7))
;
    
INSERT INTO disk
    (id, b, c, d, e)
VALUES
    (1, 'bb', 'cc', 1, 'ee'),
    (2, 'bbb', 'ccc', 2, 'eee'),
    (3, 'bbbb', 'cccc', 3, 'eeee'),
    (4, 'bbbbb', 'ccccc', 4, 'eeeee'),
    (5, 'bbbbbb', 'cccccc', 4, 'eeeeee'),
    (6, 'bbbbbbb', 'ccccccc', 5, 'eeeeeee')
;
select * FROM disk;


id
b
c
d
e


1
bb
cc
1
ee

2
bbb
ccc
2
eee

3
bbbb
cccc
3
eeee

4
bbbbb
ccccc
4
eeeee

5
bbbbbb
cccccc
4
eeeeee

6
bbbbbbb
ccccccc
5
eeeeeee


DELETE disk
FROM disk
INNER JOIN (
       SELECT id,
               d,
               CASE WHEN d = @prevd 
                       THEN @id:=@id+1
                    ELSE @id:=1
               END AS rankNum,
               @prevd:=d AS prd
       FROM disk, (SELECT @prevd:=NULL,@id:=NULL) t
      ) t1
ON disk.id = t1.id
WHERE rankNum = 2;
select * FROM disk;


id
b
c
d
e


1
bb
cc
1
ee

2
bbb
ccc
2
eee

3
bbbb
cccc
3
eeee

4
bbbbb
ccccc
4
eeeee

6
bbbbbbb
ccccccc
5
eeeeeee

id	b	c	d	e
1	bb	cc	1	ee
2	bbb	ccc	2	eee
3	bbbb	cccc	3	eeee
4	bbbbb	ccccc	4	eeeee
5	bbbbbb	cccccc	4	eeeeee
6	bbbbbbb	ccccccc	5	eeeeeee