SQL Server Determine Most Occurances Of Data In A Group

Question

Let's say I have the following:

COL1      COL2     COL3  

ABC       DATA1      
ABC       DATA1      
ABC       DATA2  
ABC       DATA3  
DEF       INFO1  
DEF       INFO2  
DEF       INFO3  
DEF       INFO3

I want to use SQL to propagate the value that occurs most often in a group into COL3 for that group. So, my results would be:

COL1      COL2     COL3  

ABC       DATA1    DATA1     
ABC       DATA1    DATA1  
ABC       DATA2    DATA1  
ABC       DATA3    DATA1  
DEF       INFO1    INFO3  
DEF       INFO2    INFO3  
DEF       INFO3    INFO3  
DEF       INFO3    INFO3

Any ideas? SQL Server 2012.

What have you attempted so far? We help fix code, we're not here to solve your problems for you. — Marc B, Aug 04 '16 at 17:40
Beth, Ties are fine - either selection would work equally as well — MichaelTFL, Aug 06 '16 at 11:48

score 0 · Answer 1 · answered Aug 04 '16 at 17:49

0

;With cte
as
(
col1,col2,row_number() over (partition by col1,col2 order by col1)  as rownum
)
select col1,col2,b.col2 as col3
from yourtable  t
cross apply
( select top1  col2 from cte c where c.col1=t.col1 order by rownum desc) b

answered Aug 04 '16 at 17:49

TheGameiswar

27,855
8
56
94

Thank you! This was very helpful! – MichaelTFL Aug 16 '16 at 13:37

score 0 · Answer 2 · answered Aug 04 '16 at 17:58

0

I would write it that way:

UPDATE P SET P.Col3=T.Col2
FROM Propagation P
JOIN(
    SELECT TOP 1 WITH TIES Col1, Col2
    FROM Propagation
    GROUP BY Col1, Col2
    ORDER BY COUNT(*) DESC
    ) T ON T.Col1=P.Col1

answered Aug 04 '16 at 17:58

Paweł Dyl

8,888
1
11
27

Thanks! The answer I developed was a combination of both these answers. – MichaelTFL Aug 16 '16 at 13:38

score 0 · Answer 3 · answered Aug 16 '16 at 13:59

Thanks to all! I used a combination of the answers to get it to work. Here is what I came up with:

WITH OCCUR_EXT
  as
    (
     SELECT [COL1], [COL2], row_number() over (partition by [COL1], [COL2] order by [COL1]) as TOT_OCCUR
       FROM [T_TABLEDATA]
      WHERE [COL2] > ''
    ),

 DOM_ASSGN
  as
    (
     SELECT *
         FROM 
         (SELECT *, 
                row_number() 
           OVER
               (partition by [COL1] order by [COL1], TOT_OCCUR DESC) as ROW_NMBR
                     FROM OCCUR_EXT ) source
                    WHERE ROW_NMBR = 1
    )

UPDATE [T_TABLEDATA]
   SET [T_TABLEDATA].[COL3] = [DOM_ASSGN].[COL2]
  FROM [T_TABLEDATA], [DOM_ASSGN]
  WHERE [T_TABLEDATA].[COL1] = [DOM_ASSGN].[COL1]
  GO

SQL Server Determine Most Occurances Of Data In A Group

3 Answers3