clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1228811 fiddles created (16681 in the last week).

CREATE TABLE T( [Data 1] INT, [Data 2] VARCHAR(5) ); INSERT INTO T VALUES (1,'x'); INSERT INTO T VALUES (3,'p'); INSERT INTO T VALUES (1,'z'); INSERT INTO T VALUES (3,'f'); INSERT INTO T VALUES (1,'x'); INSERT INTO T VALUES (1,'b'); INSERT INTO T VALUES (2,'h'); INSERT INTO T VALUES (1,'o'); INSERT INTO T VALUES (2,'h'); INSERT INTO T VALUES (1,'x'); INSERT INTO T VALUES (3,'f'); INSERT INTO T VALUES (2,'h'); INSERT INTO T VALUES (1,'z'); ;with cte as ( SELECT [Data 1], [Data 2], COUNT(*) OVER (PARTITION BY [Data 1] ORDER BY [Data 1]) [count], COUNT(*) OVER (PARTITION BY [Data 2],[Data 1] ORDER BY [Data 2]) Occurances FROM T ),cte2 as ( SELECT [Data 1], [Data 2], [count], Occurances,row_number() over(partition by [Data 1] order by Occurances desc) rn FROM cte ) select [Data 1], [Data 2], [count], Occurances from cte2 where rn = 1
Data 1 Data 2 count Occurances
1 x 7 3
2 h 3 3
3 f 3 2
 hidden batch(es)