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

this question update this question.

my column structure:

column0   column1   column2 aaa        abc      aaa        abc      aaa        abc      b aaa        abc      aaa        abc      b aaa        abc      na aaa        xyx      b aaa        na       b bbb        fgh      v bbb        fgh      na bbb        fgh      na bbb        na       m bbb        na       m bbb        na       m bbb        na       na bbb        na       na ccc        na       na ccc        na       na ccc        na       na 

what wished earlier 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.

the same rule applies here well. 'column2', wish apply same rule on expected answer of 'column1'

so though 'column0' value 'aaa' number of 'column2' values b more, wish answer 'column2' a. because query result in 'column1' 'abc' 'column0' value 'aaa' , amongst said 'column1' values in 'column2', 'a' more.

similarly, though 'column0' value 'bbb' number of 'column2' values m , na more, wish answer 'column2' v.

as earlier not take value na account unless values na.

so expected value:

column0   column1   column2 aaa       abc       bbb       fgh       v ccc       na        na 

all sincerely appreciated

thanks

same used in responses of previous question, can use count window function, add count partitioned on columns, , ordering row_number first on prevalent values in column1, , on prevalent values of both columns.

;with counts (     select column0, column1, column2,            count(nullif(column1,'na')) on (partition column0, column1) cntcol1,            count(nullif(column2,'na')) on (partition column0, column1,  column2) cntcol2     @t ) , ranked (     select column0, column1, column2,            row_number() on (partition column0  order cntcol1 desc, cntcol2 desc) rnr     counts ) select column0, column1, column2 ranked rnr = 1 

the column2 records inside prevalent column1 records because first order on cntcol1 forces records come first , because cntcol2 based on both column2 , column1, value based on count of column2 inside column1 (and column0 primary partition).

nullif(column,'na') used force 'na' lower value on both counts.