How to group items by path (graph structure) and leave only those with highest score using t-sql? I have a table with records and their scores (table 'Offers'):

OfferId     OfferScore
----------- -----------
1           1
2           20
3           3
4           4
5           5
6           6
7           7
8           8


These records might be related. All relations are stored in the table 'OffersRelation':

OfferId1    OfferId2
----------- -----------
1           2
1           3
1           4
1           5
2           3
3           5
7           8


I want to remove all related Ids (within the group) from table Offers and leave only ones with the highest score.

In my case :

Offers with Ids: 1,2,3,4,5 are related ("group") and the offer with id:2 has the highest score:20 so I want to remove ids: 1,3,4,5 and leave only the one with id:2 from this "group".

Offer with Id:6  doesn't have any related items (no relations in the table OffersRelations) so it stays in the table Offers.

Offers with Ids:7,8 are related and Offer with id:8 has higher score than 7 so i want to remove the offer with id:7 and leave the offer with id:8 from this "group".

Expected result in the table Offers:

OfferId     OfferScore
----------- -----------
2           20
6           6
8           8


How to do it in t-sql?

Data from examples:

drop table if exists dbo.Offers
drop table if exists dbo.OffersRelation
CREATE TABLE [dbo].[Offers](
    [OfferId] [int] NOT NULL,
    [OfferScore] [int] NOT NULL
) 
GO

CREATE TABLE [dbo].[OffersRelation](
    [OfferId1] [int] NOT NULL,
    [OfferId2] [int] NOT NULL
) 
GO

INSERT INTO [dbo].[Offers]
           ([OfferId],[OfferScore])
     VALUES (1,1),(2,20),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8)


INSERT INTO [dbo].[OffersRelation]
           ([OfferId1]
           ,[OfferId2])
     VALUES
            (1,2),
            (1,3),
            (1,4),
            (1,5),
            (2,3),
            (3,5),
            (7,8)
I have completely redone my answer (based on the additional information you provided in your question).  

You indicated that you tried recursion and it was too slow on a large set of data.  I've created a cursor based solution that works on your test data, but I'm wondering if it gives you the correct answer on your production data.  With such a small set of test data, it's difficult to spot flaws in my logic.

Can you give it a try and let me know if I'm any closer to a proper solution?  

DECLARE @OfferId INT
DECLARE @OffersToDelete TABLE (id INT)

--Declare cursor for all Offers
DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT [o].offerid
FROM Offers [o]
ORDER BY [o].OfferId

OPEN _CURSOR

FETCH NEXT
FROM _CURSOR
INTO @OfferId

DECLARE @Related TABLE (OfferId INT)

WHILE @@FETCH_STATUS = 0
BEGIN
    --For each Offer, initialize @Related table
    DELETE
    FROM @Related

    --Insert into @Related the Offerid that we are currently processing
    INSERT INTO @Related
    SELECT [or].OfferId2
    FROM OffersRelation [or]
    WHERE [or].OfferId1 = @OfferId

    --Now, loop through all offers related to the one we're processing
    --When @@ROWCOUNT = 0, you have processed all related rows and need
    --to break out of the while loop
    WHILE 1 = 1
    BEGIN
        INSERT INTO @Related
        SELECT [or].OfferId2
        FROM OffersRelation [or]
        JOIN @Related [r]
            ON [r].OfferId = [or].OfferId1
        WHERE NOT EXISTS (
                SELECT *
                FROM @Related
                WHERE OfferId = [or].OfferId2
                )
            AND [r].OfferId &lt;&gt; @OfferId

        IF @@ROWCOUNT = 0
            BREAK;
    END;

    --With all related rows, including the offer we're processing,
    --add a row number ordered by OfferScore desc and insert all rows
    --not = 1 into the @OffersToDelete table
    WITH OfferAndRelated
    AS (
        SELECT Offerid
            ,offerscore
        FROM offers
        WHERE offerid = @OfferId

        UNION

        SELECT [o].offerid
            ,[o].offerscore
        FROM @Related [r]
        JOIN Offers [o]
            ON [o].OfferId = [r].OfferId
        )
        ,OfferAndRelatedWithRowNumber
    AS (
        SELECT *
            ,ROW_NUMBER() OVER (
                ORDER BY OfferScore DESC
                ) AS rn
        FROM OfferAndRelated
        )
    INSERT INTO @OffersToDelete
    SELECT OfferId
    FROM OfferAndRelatedWithRowNumber
    WHERE rn &lt;&gt; 1

    --  SELECT *
    --  FROM @Related
    FETCH NEXT
    FROM _CURSOR
    INTO @OfferId
END

CLOSE _CURSOR

DEALLOCATE _CURSOR

--Delete from Offers where the OfferId is on the @OffersToDelete table
DELETE [o]
FROM Offers [o]
JOIN @OffersToDelete otd
    ON otd.id = [o].OfferId

SELECT *
FROM Offers




| OfferId | OfferScore |
|---------|------------|
| 2       | 20         |
| 6       | 6          |
| 8       | 8          |