By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * into t from (
select 10,'a',4 union --- new entry
select 9,'a',3 union
select 8,'a',2 union
select 7,'b',3 union
select 6,'a',1 union -- need to delete
select 5,'b',2 union
select 4,'c',3 union
select 3,'c',2 union
select 2,'b',1 union
select 1,'c',1
) door (sno,id, N_th_Reocord)
10 rows affected
SELECT * FROM t
| sno | id | N_th_Reocord |
|---|---|---|
| 1 | c | 1 |
| 2 | b | 1 |
| 3 | c | 2 |
| 4 | c | 3 |
| 5 | b | 2 |
| 6 | a | 1 |
| 7 | b | 3 |
| 8 | a | 2 |
| 9 | a | 3 |
| 10 | a | 4 |
WITH cte AS(
SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY sno DESC) rn
FROM t
)
DELETE FROM cte
WHERE rn > 3
1 rows affected
SELECT * FROM t
| sno | id | N_th_Reocord |
|---|---|---|
| 1 | c | 1 |
| 2 | b | 1 |
| 3 | c | 2 |
| 4 | c | 3 |
| 5 | b | 2 |
| 7 | b | 3 |
| 8 | a | 2 |
| 9 | a | 3 |
| 10 | a | 4 |