前6位的SQL计数值

请出示这个样品。

 SELECT
   [Number]
  ,[Over]
  ,[Goal]
  , SUM( If(OVER= 'Y',1,0)) AS CNT
 FROM [Test_Hermes].[dbo].[Test]
 ORDER BY Number desc
LIMIT 1,6;

不能在子查询中使用order by。

SELECT COUNT([t1].[Over]) 
FROM (SELECT TOP 6 [Number]
    ,[Over] 
    ,[Goal]
  FROM [Test_Hermes].[dbo].[Test]) as t1
WHERE [OVER] = 'Y' ;

SELECT COUNT([t1].[Over]) 
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY [Number] ORDER BY [Number] DESC) as 'Rank'
    ,[Number]
    ,[Over] 
    ,[Goal]
  FROM [Test_Hermes].[dbo].[Test]) as t1 

WHERE [t1].[OVER] = 'Y' AND [t1].[Rank] <= 6;