Postgres 12

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

CREATE TABLE tablename (Block_id int, step int, name varchar(6));
    
INSERT INTO tablename (Block_id, step, name) VALUES
    (1, 1, 'Marie'),
    (1, 2, 'Bob'),
    (1, 3, 'John'),
    (1, 4, 'Lola'),
    (2, 1, 'Alex'),
    (2, 2, 'John'),
    (2, 3, 'Kate'),
    (2, 4, 'Herald'),
    (3, 1, 'Alec'),
    (3, 2, 'Paul'),
    (3, 3, 'Rex');

CREATE TABLE



INSERT 0 11


WITH cte AS (
  SELECT Block_id, MIN(step) step
  FROM tablename
  WHERE name = 'John'
  GROUP BY Block_id
)
DELETE FROM tablename t
USING cte c
WHERE c.Block_id = t.Block_id AND c.step <= t.step

DELETE 5


SELECT *
FROM tablename 


block_id
step
name


1
1
Marie

1
2
Bob

2
1
Alex

3
1
Alec

3
2
Paul

3
3
Rex



SELECT 6