Say I have a data Table, two columns
How would I write code in SQL that shows the top two co-occuring colors. So the output should be a table with two columns (color and count) . ex "Green","Red" 3, "Blue" "Blue" 2, "Pink" "Purple" 1. I'm working with a big dataset so I would want something a little simpler to use.
declare @t table ( person varchar(100), color varchar(100) ); insert into @t values ('Bob', 'Green'), ('Bob', 'Red'), ('Mark', 'Blue'), ('Mark', 'Blue'), ('Sally', 'Pink'), ('Sally', 'Purple'), ('Kat', 'Green'), ('Kat', 'Red'), ('Mike', 'Green'), ('Mike', 'Red'), ('Pat', 'Blue'), ('Pat', 'Blue'); with t as ( select t.*, row_number() over (partition by person order by color) rn from @t t ) select t1.color color1, t2.color color2, count(*) cnt from t t1 inner join t t2 on t1.person = t2.person and t1.rn > t2.rn group by t1.color, t2.color order by cnt desc;