如何为每个组选择最高x记录 [英] How to select top x records for every group

问题描述

 选择Id，UserId from myTable group by Id，UserId COUNT（UserId）< 7 
  

现在我想要为每个userid选择6条记录。但是我的方法失败了。

那么正确的语法是什么？

Id是主键聚集索引

解决方案

$ c $> WITH r（userid，rnk）
AS（SELECT userid，RANK（）OVER（PARTITION BY id）AS rnk
FROM MyTable
GROUP BY userid）
SELECT r 。*
FROM r
WHERE r.Rank <= 6

I tried something like this

select Id,UserId from myTable group by Id,UserId having COUNT(UserId)<7

Now what i want to do is selecting 6 records for each userid. But my approach failed.

So what is the correct syntax ?

Id is primary key clustered index

解决方案

This should get you pretty close

WITH    r ( userid, rnk )
      AS ( SELECT   userid, RANK() OVER ( PARTITION BY id ) AS rnk
           FROM     MyTable 
           GROUP BY userid)
SELECT  r.*
FROM    r
WHERE   r.Rank <= 6 

这篇关于如何为每个组选择最高x记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！