SQL Server 2017

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

create table redshift(guest_id int, name varchar(20), rownum int);

insert into redshift values (1, 'Safvan', 1), (1, 'Safvan', 2), (1, 'Thomas', 3),
(2, 'Anandu', 1), (2, 'Manish', 2);
5 rows affected

SELECT *
FROM redshift


guest_id
name
rownum


1
Safvan
1

1
Safvan
2

1
Thomas
3

2
Anandu
1

2
Manish
2


WITH t_cte AS (
       SELECT *
            , ROW_NUMBER() OVER (PARTITION BY guest_id ORDER BY rownum DESC) row_num
       FROM redshift
)
DELETE redshift 
FROM t_cte c
INNER JOIN redshift r
        ON c.guest_id = r.guest_id
       AND  c.row_num > 1 AND c.rownum = r.rownum
3 rows affected

SELECT *
FROM redshift


guest_id
name
rownum


1
Thomas
3

2
Manish
2