How to calculate the mode for each value in another column in TSQL

sql-server, sql-server-2008, t-sql, window-functions

Solution

Declare @t Table(Id Int Identity, [Group] Varchar(1),Gender Varchar(1))
    Insert Into @t Values
    ('A','M'),('A','M'),('A','F'),('A','M'),('A','U'),
    ('B','F'),('B','F'),('B','M'),
    ('C','U'),('C','F'),('C','U')

;With Cte As 
(
    Select 
        [Group]
        ,Gender
        ,GenderCount = Count(Gender)
    From @t
    Group By [Group],Gender
)

Select Gender,ModeGroup = [Group]
From (
        Select 
            *,
        Rn = Dense_Rank() Over(Partition by [Group] order by [Group],GenderCount desc)
        from Cte
     )X
Where Rn =1

Result

Gender  ModeGroup
M       A
F       B
U       C

Problem

I have a table like this: ``` ID Group Gender ------------------ 1 A M 2 A M 3 A F 4 A M 5 A U 6 B F 7 B F 8 B M 9 C U 10 C F 11 C U ``` I am trying to calculate the mode group for each gender. In other words, for each gender, tell me which is the most popular group. So the results I want would be as follows: ``` Gender ModeGroup ----------------- M A (because 3 males in group A, 1 in B and 0 in C) F B (because 2 females in group B, 1 in A and 1 in C) U C (because 2 unknown in group C, 0 in B and 1 in C) ``` In the case of a tie, I need a record returned for each of the tied groups. How can I do this elegantly in TSQL? I think I need to use a window function, but I've been struggling with how to go about it.

Original source