MSSQL，如何查询出现频率最高的唯一记录？
MSSQL，如何查询出现频率最高的唯一记录？
例如，下述查询得知h_2中出现频率最高的是12为167次，如何只得出h_2 = 12 s_cq = 167 ？
SELECT h_2 , count(h_2) as s_cq FROM [TCFX].[dbo].[C1]
group by h_2
order by s_cq desc
h_2s_cq
12167
10165
11137
9122
8107
782
680
542
438
325
219
[解决办法]
SELECT top 1 h_2 , count(h_2) as s_cq FROM [TCFX].[dbo].[C1]
group by h_2
order by s_cq desc
SELECT top 1 h_2,count(h_2) 's_cq'
FROM [TCFX].[dbo].[C1]
group by h_2
order by s_cq desc
select h_2,s_cq
from
(
SELECT h_2 ,count(h_2) as s_cq,
row_number() over(order by count(h_2) desc) as rownum
FROM [TCFX].[dbo].[C1]
group by h_2
)t
where t.rownum = 1
SELECT top 1 h_2 , count(h_2) as s_cq FROM [TCFX].[dbo].[C1]
group by h_2
order by s_cq desc
