sql - Get row for each user where the count of a value in a column is maximum -

my column structure:

column0   column1 aaa        abc aaa        abc aaa        xyx aaa        na bbb        fgh bbb        na bbb        na bbb        na ccc        na ccc        na ccc        na ccc        na 

what wish foreach distinct 'column0' data 'column1' data count max unless data na in case second highest. if 'column0' data values of 'column1' na value can na

so expected value:

column0   column1 aaa       abc bbb       fgh ccc       na 

this give correct result:

declare @t table(column0  char(3), column1 varchar(3)) insert @t values ('aaa','abc'),('aaa','abc'),('aaa','xyx'),('aaa','na') ,('bbb','fgh'),('bbb','na'),('bbb','na'),('bbb','na') ,('ccc','na'),('ccc','na'),('ccc','na'),('ccc','na')  ;with cte (   select     column0,      column1,      count(case when column1 <> 'na' 1 end) on (partition column0, column1) cnt   @t ), cte2 (   select      column0,      column1,      row_number() on (partition column0 order cnt desc) rn   cte ) select column0, column1 cte2 rn = 1 

result:

column0  column1 aaa      abc bbb      fgh ccc      na