MSSQL，如何查询出现频率最高的唯一记录？

[my_yinger]

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_2 s_cq

12 167
10 165
11 137
9 122
8 107
7 82
6 80
5 42
4 38
3 25
2 19

[my_yinger]

SELECT h_2 , count(h_2) as s_cq FROM [TCFX].[dbo].[C1] group by h_2 order by s_cq desc h_2 s_cq 12 167 10 165 11 137 9 122 8 107 7 82 6 80 5 42 4 38 3 25 2 19

[LongRui888]

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

[LongRui888]

这种适合sql server 2005及以后的版本：

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

[Landa_Aiden]

SELECT top 1 h_2 , count(h_2) as s_cq  FROM [TCFX].[dbo].[C1]
group by h_2
order by s_cq desc