窗口函数 嵌套查询 TopN避坑

前两天写一个【求各类别下出现次数最多的前两个品牌】的SQL，一直报2附近有语法错误。

今天将此记录一下

我写的

SELECT *
FROM (
SELECT *,
row_number() over(partition by a.categoryname ORDER BY a.brandcount DESC) as ranking
FROM 
(SELECT categoryname,brandname,count(brandname)as brandcount 
FROM vw_sku_basic_info GROUP BY categoryname,brandname))
as a WHERE ranking <= 2

报错信息

SELECT *
FROM (
SELECT *,
row_number() over(partition by a.categoryname ORDER BY a.brandcount DESC) as ranking
FROM (SELECT categoryname,brandname,count(brandname)as brandcount FROM vw_sku_basic_info GROUP BY categoryname,brandname))as a WHERE ranking <= 2
> [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]')' 附近有语法错误。 (102)

正确的 

SELECT *
FROM (
SELECT *,
row_number() over(partition by a.categoryname ORDER BY a.brandcount DESC) as ranking