Microsoft SQL server选择Top N组。

There are a lot of answers about how to select n rows from each group. But what I am looking for is to select every row from top N group, for example I have the data below:

关于如何从每个组中选择n行有很多答案。但是我要找的是从上面N组中选择每一行，例如我有下面的数据:

 id   group
  1     a
  2     a
  3     b
  4     c
  5     c
  6     d
  7     d
  .......

If I want to select Top 3 Group, my intended results as below:

如果我想选择前3组，我的预期结果如下:

How can I achieve this with Microsoft SQL server 2008?

如何使用Microsoft SQL server 2008实现这一点?

3 个解决方案

#1

One option is to use a subquery which selects the top N groups:

一种选择是使用一个子查询来选择前N组:

SELECT t1.id, t1.group
FROM yourTable t1
INNER JOIN
(
    SELECT DISTINCT TOP(N) group
    FROM yourTable
    ORDER BY group
) t2
    ON t1.group = t2.group

#2

You could rank your rows by the group and then take only the top three:

你可以按组排列你的排，然后只取前三名:

SELECT [id], [group]
FROM   (SELECT [id], [group], RANK() OVER (ORDER BY [group] ASC) rk
        FROM   mytable) t
WHERE  rk <= 3

#3

@Tim: I just modified your query.

我只是修改了你的查询。

SELECT t1.id, t1.group
FROM yourTable t1
INNER JOIN
(
    SELECT TOP N group
    FROM yourTable
    GROUP BY group
    --ORDER BY group USE IT IF YOU WANT
) t2
    ON t1.group = t2.group

#1