数据表MatchNumLog有字段：ID（自动编号），RuleID，MatchNum，Frequency（MatchNum的出现次数）
主键为RuleID和MatchNum,数据如下所示：
ID（自动编号） RuleID MatchNum Frequency
1 001 1 3
2 001 2 5
3 002 3 1
4 002 4 10
5 002 1 6
若要求各个RuleID出现次数最多（Frequency最大）的MatchNum，SQL语句该如何写？谢谢！
如上例要求的结果为：
2 001 2 5
4 002 4 10
------解决方案--------------------
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[RuleID] nvarchar(3),[MatchNum] int,[Frequency] int)
Insert #T
select 1,N'001',1,3 union all
select 2,N'001',2,5 union all
select 3,N'002',3,1 union all
select 4,N'002',4,10 union all
select 5,N'002',1,6
Go
SELECT *
FROM
(Select *,COUNT(*)OVER(PARTITION BY [RuleID]) con,ROW_NUMBER()OVER(PARTITION BY [RuleID] ORDER BY [Frequency] desc) AS row from #T AS a )t
WHERE row=1
/*
ID RuleID MatchNum Frequency con row
2 001 2 5 2 1
4 002 4 10 3 1
*/
------解决方案--------------------
select * from tb a
where id=(select top 1 id from tb where RuleID=a.RuleID order by MatchNum desc,Frequency desc)
------解决方案--------------------
select t.* from MatchNumLog t where Frequency = (select max(Frequency) where RuleID = t.RuleID)
select t.* from MatchNumLog t where not exists (select 1 where RuleID = t.RuleID and Frequency > t.Frequency)
------解决方案--------------------
select t.* from MatchNumLog t where Frequency = (select max(Frequency) from MatchNumLog where RuleID = t.RuleID)