I am using SQL Server and I'm trying to find out the top 2 products that are being bought the most together

This is a product table

I want it to be displayed as shown below:

I have tried

SELECT TOP 2 Product_Id, bought_with_Product_Id, times_bought_together 
FROM PRODUCT
GROUP BY Product_Id, bought_with_Product_Id, times_bought_together

Have tried this as well

SELECT TOP 2 *
FROM Product
WHERE times_bought_together = (SELECT MAX(times_bought_together) FROM product)
AND Product_Id <> bought_with_Product_Id

it returns

How do I make it so that product_id and bought_with_product_Id rows do not overlap

CodePudding user response：

You can exclude the duplicate rows using a NOT EXISTS test e.g.

declare @Test table (id int, otherId int, times int);

insert into @Test (id, otherId, times)
values
(1,2,3),
(2,1,3),
(4,1,2),
(1,4,2),
(1,5,1),
(5,1,1);

select top 2 *
from @Test T1
where not exists (
    select 1
    from @Test T2
    where T1.id = T2.otherId
    and T1.otherId = T2.id
    -- Keep the duplicate with the lower id
    and T2.id < T1.id
);

Returns:

Note: Providing the DDL DML for your test data (as shown here) makes it much easier for people to answer your question.