SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- source data
CREATE TABLE dis (id INT, kaf INT);
INSERT INTO dis VALUES 
(1,11),
(1,12),
(2,15),
(2,16),
(3,13),
(3,14),
(3,15),
(4,11),
(4,12),
(5,13),
(5,14),
(5,15);
SELECT * FROM dis ORDER BY kaf, id;


id
kaf


1
11

4
11

1
12

4
12

3
13

5
13

3
14

5
14

2
15

3
15

5
15

2
16


-- look at ids list
WITH cte AS ( SELECT kaf, STRING_AGG(id, ',') WITHIN GROUP (ORDER BY id) ids
              FROM dis
              GROUP BY kaf )
SELECT * FROM cte ORDER BY kaf;


kaf
ids


11
1,4

12
1,4

13
3,5

14
3,5

15
2,3,5

16
2


-- delete excess kafs
WITH cte AS ( SELECT kaf, STRING_AGG(id, ',') WITHIN GROUP (ORDER BY id) ids
              FROM dis
              GROUP BY kaf )
DELETE dis
FROM dis
JOIN cte cte1 ON dis.kaf=cte1.kaf
JOIN cte cte2 ON cte1.ids = cte2.ids AND cte1.kaf > cte2.kaf;
SELECT * FROM dis ORDER BY kaf, id;


id
kaf


1
11

4
11

3
13

5
13

2
15

3
15

5
15

2
16


-- check the result
WITH cte AS ( SELECT kaf, STRING_AGG(id, ',') WITHIN GROUP (ORDER BY id) ids
              FROM dis
              GROUP BY kaf )
SELECT * FROM cte ORDER BY kaf;


kaf
ids


11
1,4

13
3,5

15
2,3,5

16
2