Holen Sie sich Zeile für jeden Benutzer, wo die Zählung eines Wertes in einer Spalte maximal ist

Meine Spaltenstruktur:

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 

Was ich mir wünsche, ist vorher auf "Column0" data 'Column1' data, deren Zählung maximal ist, wenn diese data NA sind, in diesem Fall die zweithöchste. Wenn für ein 'Column0' data alle Werte von 'Column1' NA sind, dann kann der Wert NA sein

Also erwarteter Wert:

 Column0 Column1 aaa abc bbb fgh ccc NA 

Dies ergibt das richtige Ergebnis:

 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 as ( SELECT column0, column1, count(case when column1 <> 'NA' THEN 1 end) over (partition by column0, column1) cnt FROM @t ), CTE2 as ( SELECT column0, column1, row_number() over (partition by column0 order by cnt desc) rn FROM CTE ) SELECT column0, column1 FROM CTE2 WHERE rn = 1 

Ergebnis:

 column0 column1 aaa abc bbb fgh ccc NA 

Sie können zwei CTEs und die Rangfolge ROW_NUMBER :

 WITH CTE1 AS ( SELECT Column0, Column1, Cnt = COUNT(*) OVER (PARTITION BY Column0, Column1) FROM dbo.TableName ) , CTE2 AS ( SELECT Column0, Column1, RN = ROW_NUMBER() OVER (PARTITION BY Column0 ORDER BY CASE WHEN Column1 = 'NA' THEN 1 ELSE 0 END ASC , Cnt DESC) FROM CTE1 ) SELECT Column0, Column1 FROM CTE2 WHERE RN = 1 

Demo

Wie wäre es mit so etwas?

 select T1.Column0, isnull(( select top(1) T2.Column1 from dbo.YourTable as T2 where T1.Column0 = T2.Column0 and T2.Column1 <> 'NA' group by T2.Column1 order by count(*) desc ), 'NA') as Column1 from dbo.YourTable as T1 group by T1.Column0 

SQL-Geige

Und mit einem Index

 create index IX_YourTable_Column0 on YourTable(Column0, Column1) 

Du bekommst einen netten Abfrageplan.

Bildbeschreibung hier eingeben

Und eine Version, die mit NULL-Werten in Column0 .

 select T1.Column0, isnull(( select top(1) T2.Column1 from dbo.YourTable as T2 where exists(select T1.Column0 intersect select T2.Column0) and T2.Column1 <> 'NA' group by T2.Column1 order by count(*) desc ), 'NA') as Column1 from dbo.YourTable as T1 group by T1.Column0 

Der Abfrageplan für diese Version ist der gleiche wie der oben.

Sie können row_number() mit einer Aggregation verwenden:

 select column0, column1 from (select column0, column1, row_number() over (partition by column0 order by count(*) desc ) as seqnum from [table] group by column0, column1 ) t where seqnum = 1; 

Wenn Sie im Falle von Bindungen Duplikate zulassen möchten, dann verwenden Sie rank() oder dense_rank() anstelle von row_number() .