Select *,RANK() over (partition by 分组列名 order by 排序列名) as rn from [Table]
例如：获取按…分组的按…排序的每组的前2条记录
select Table1.* from
(
select *,RANK()
over (partition by a.列1,a.列2, order by a.列3) as rn
from Table1
) as Table2
where rn < 3
原文：http://www.cnblogs.com/xieyuanzheng/p/5597067.html