SQLite 3.27

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

CREATE TABLE test (id INT, name VARCHAR(255), value INT);
✓
INSERT INTO test VALUES
(0,	'Masha',	12),
(1,	'Sasha',	58),
(2,	'Sasha',	58),
(3,	'Sasha',	58),
(4,	'Dima', 	74),
(5,	'Tanya',	12),
(6,	'Tanya',	12),
(7,	'Sasha',	58),
(8,	'Masha',	44),
(9,	'Sasha',	58),
(10,	'Sasha',	58),
(11,	'Sasha',	58);
✓
SELECT * FROM test;


id
name
value


0
Masha
12

1
Sasha
58

2
Sasha
58

3
Sasha
58

4
Dima
74

5
Tanya
12

6
Tanya
12

7
Sasha
58

8
Masha
44

9
Sasha
58

10
Sasha
58

11
Sasha
58


WITH cte AS (
SELECT id, 
       name = LAG(name) OVER (ORDER BY id) AND value = LAG(value) OVER (ORDER BY id) flag
from TEST
)
DELETE
FROM test
WHERE EXISTS ( SELECT NULL
               FROM cte
               WHERE test.id = cte.id AND flag)
✓
SELECT * FROM test;


id
name
value


0
Masha
12

1
Sasha
58

4
Dima
74

5
Tanya
12

7
Sasha
58

8
Masha
44

9
Sasha
58

id	name	value
0	Masha	12
1	Sasha	58
2	Sasha	58
3	Sasha	58
4	Dima	74
5	Tanya	12
6	Tanya	12
7	Sasha	58
8	Masha	44
9	Sasha	58
10	Sasha	58
11	Sasha	58