Discovering the most common combinations

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Discovering the most common combinations

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-02-27 : 09:29:12

Marcelo writes "Hello, the situation is, I have one table (the result of combining two) that has the following attributes

T1
transactionId
productId

When I query the table I get something like this:

T1
0 1
0 2
0 5
1 2
1 5
1 6
2 6
3 1
3 5
etc

This would mean that in the first transaction products 1, 2, and 5 were bought; and so on for the rest. I need to "discover" the most common combination across all transactions (e.g., above it would be products 2, 5 and 1, 5)

Is there a way to achieve this?"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-27 : 11:04:11

Yes, you need to do a self join on the table, here's an example:

declare @temp table(TransactionID int, ProductID int)
insert @temp
select 0, 1 union all
select 0, 2 union all
select 0, 5 union all
select 1, 2 union all
select 1, 5 union all
select 1, 6 union all
select 2, 6 union all
select 3, 1 union all
select 3, 5

select t1.ProductID, t2.ProductID, count(*) 
from @temp t1
inner join @temp t2 on t1.TransactionID = t2.TransactionID and t1.ProductID < t2.ProductID
group by t1.ProductID, t2.ProductID
order by 3 desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources