如何在SQL查询中为每个组选择第一行？

I've got this SQL query:

我有这个SQL查询：

SELECT   Foo, Bar, SUM(Values) AS Sum
FROM     SomeTable
GROUP BY Foo, Bar
ORDER BY Foo DESC, Sum DESC

This results in an output similar to this:

这导致输出类似于：

47  1   100
47  0   10
47  2   10
46  0   100
46  1   10
46  2   10
44  0   2

I'd like to have only the first row per Foo and ignore the rest.

我希望每个Foo只有第一行而忽略其余的。

47  1   100
46  0   100
44  0   2

How do I do that?

我怎么做？

9 个解决方案

#1

declare @sometable table ( foo int, bar int, value int )

insert into @sometable values (47, 1, 100)
insert into @sometable values (47, 0, 10)
insert into @sometable values (47, 2, 10)
insert into @sometable values (46, 0, 100)
insert into @sometable values (46, 1, 10)
insert into @sometable values (46, 2, 10)
insert into @sometable values (44, 0, 2)

;WITH cte AS 
(
    SELECT   Foo, Bar, SUM(value) AS SumValue, ROW_NUMBER() OVER(PARTITION BY Foo ORDER BY FOO DESC, SUM(value) DESC) AS RowNumber
    FROM     @SomeTable
    GROUP BY Foo, Bar
)
SELECT * 
FROM cte
WHERE RowNumber = 1

#2

I might disagree with rjmunru in that using Ansii style joins can often be easier to read than subqueries but to each his own -- I just follow what our DBAs say to do.

我可能不同意rjmunru，因为使用Ansii样式连接通常比子查询更容易阅读，但对于他自己的每个 - 我只是按照我们的DBA所说的去做。

If you just want the first result from a query, you might be able to use a rownum (if using oracle, other databases probably have something similiar).

如果您只想要查询的第一个结果，您可能可以使用rownum（如果使用oracle，其他数据库可能有类似的东西）。

select * from foo_t f where f.bar = 'bleh' and rownum = 1

select * from foo_t f其中f.bar ='bleh'和rownum = 1

Of course a HAVING clause might also be appropriate, depending on what you are trying to do.

当然，HAVING子句也可能是合适的，具体取决于您要做的事情。

"HAVING is used to perform an action on groups created by GROUP BY similar to that of the WHERE clause on rows in a basic SQL statement. The WHERE clause limits the rows evaluated. The HAVING clause limits the grouped rows returned."

“HAVING用于对GROUP BY创建的组执行类似于基本SQL语句中行的WHERE子句的操作.WHERE子句限制评估的行.HAVING子句限制返回的分组行。”

hth

心连心

#3

Just group on Players.Nick alone, and select the first (min) of the description

只需将Players.Nick单独分组，然后选择描述的第一个（最小）

SELECT     Players.Nick, MIN(Reasons.Description), SUM(Marks.Value) AS Sum
FROM         Marks INNER JOIN
                      Players ON Marks.PlayerID = Players.ID INNER JOIN
                      Reasons ON Marks.ReasonId = Reasons.ID
GROUP BY Players.Nick
ORDER BY Players.Nick, Sum DESC

that is if you always want the first without knowing it

那就是如果你总是想要第一个不知道它

#4

It's an old post but I had the same problem today. I've solved it by trying many queries until it works. I'm using SQL Compact 3.5 with visual basic 2010.

这是一个老帖子，但今天我遇到了同样的问题。我通过尝试许多查询来解决它，直到它工作。我正在使用SQL Compact 3.5和visual basic 2010。

This example is for a table named "TESTMAX" with columns "Id" (primary key), "nom" (name) and "value", you can use this to obtain rows with max "value" for each "nom" :

此示例适用于名为“TESTMAX”的表，其中包含“Id”（主键），“nom”（名称）和“value”列，您可以使用此表来获取每个“nom”具有最大“值”的行：

SELECT TESTMAX.Id, TESTMAX.NOM, TESTMAX.Value
FROM     TESTMAX INNER JOIN
                  TESTMAX AS TESTMAX_1 ON TESTMAX.NOM = TESTMAX_1.NOM
WHERE  (TESTMAX.Value IN
                      (SELECT MAX(Value) AS Expr1
                       FROM      TESTMAX AS TESTMAX_2
                       WHERE   (NOM = TESTMAX_1.NOM)))
GROUP BY TESTMAX.Id, TESTMAX.NOM, TESTMAX.Value

If you want to delete the other rows, you can also use :

如果要删除其他行，还可以使用：

DELETE FROM TESTMAX
WHERE  (Id NOT IN
                      (SELECT TESTMAX_3.Id
                       FROM      TESTMAX AS TESTMAX_3 INNER JOIN
                                         TESTMAX AS TESTMAX_1 ON TESTMAX_3.NOM = TESTMAX_1.NOM
                       WHERE   (TESTMAX_3.Value IN
                                             (SELECT MAX(Value) AS Expr1
                                              FROM      TESTMAX AS TESTMAX_2
                                              WHERE   (NOM = TESTMAX_1.NOM)))
                       GROUP BY TESTMAX_3.Id, TESTMAX_3.NOM, TESTMAX_3.Value))

#5

In general, try using Subqueries rather than joining and grouping - it often makes SQL that is much easier to understand.

通常，尝试使用子查询而不是连接和分组 - 它通常使SQL更容易理解。

SELECT Nick,
   (SELECT Description from Reasons WHERE Reasons.ID = (
       SELECT FIRST(Marks.ReasonId) from Marks WHERE Marks.PlayerID = Players.ID)
   ),
   (SELECT SUM(Value) from Marks WHERE Marks.PlayerID = Players.ID)

#6

Is this an opportunity to use a 'HAVING' clause ? (You want to discriminate on an aggregate function - 'Sum') ?

这是一个使用'HAVING'条款的机会吗？ （你想区分聚合函数 - 'Sum'）？

#7

(EDITED Based on edited question) Then, since you wish to filter based on the value of an aggregated column, what you need is a Having Clause.

（已编辑基于已编辑的问题）然后，由于您希望根据聚合列的值进行过滤，因此您需要的是具有条款。

  SELECT p.Nick, r.Description, SUM(m.Value) Sum
  FROM Marks m
    JOIN Players p
      ON m.PlayerID = p.ID 
    JOIN Reasons r 
      ON m.ReasonId = r.ID
  GROUP BY p.Nick, r.Description
  Having SUM(m.Value) =
      (Select Max(Sum) From
        (SELECT SUM(m.Value) Sum
         FROM Marks mi
           JOIN Players pi
              ON mi.PlayerID = pi.ID 
           JOIN Reasons r i
             ON mi.ReasonId = ri.ID
         Where pi.Nick = p.Nick
         GROUP BY pi.Nick, ri.Description))

  Order By p.Nick, Sum Desc

#8

Curious. Only way I could get this to work was by using a temporary holding table in memory. (TSQL syntax)

好奇。只有通过在内存中使用临时保存表才能使其工作。 （TSQL语法）

-- original test data
declare @sometable table ( foo int, bar int, value int )

insert into @sometable values (1, 5, 10)
insert into @sometable values (1, 4, 20)
insert into @sometable values (2, 1, 1)
insert into @sometable values (2, 1, 10)
insert into @sometable values (2, 1, 1)
insert into @sometable values (2, 2, 13)
insert into @sometable values (3, 4, 25)
insert into @sometable values (3, 5, 1)
insert into @sometable values (3, 1, 1)
insert into @sometable values (3, 1, 1)
insert into @sometable values (3, 1, 1)
insert into @sometable values (3, 1, 1)
insert into @sometable values (3, 1, 1)

-- temp table for initial aggregation
declare @t2 table (foo int, bar int, sums int)
insert into @t2
select foo, bar, sum(value) 
from @sometable
group by foo, bar

-- final result
select foo, bar, sums
from @t2 a
where sums = 
    (select max(sums) from @t2 b 
     where b.foo = a.foo)

#9

SQL Server 2005 you can use this:

SQL Server 2005你可以使用这个：

declare @sometable table ( foo int, bar int, value int )

声明@sometable表（foo int，bar int，value int）

insert into @sometable values (1, 5, 10) insert into @sometable values (1, 4, 20) insert into @sometable values (2, 1, 1) insert into @sometable values (2, 1, 10) insert into @sometable values (2, 1, 1) insert into @sometable values (2, 2, 13) insert into @sometable values (3, 4, 25) insert into @sometable values (3, 5, 1) insert into @sometable values (3, 1, 1) insert into @sometable values (3, 1, 1) insert into @sometable values (3, 1, 1) insert into @sometable values (3, 1, 1) insert into @sometable values (3, 1, 1)

插入@sometable值（1,5,10）插入@sometable值（1,4,20）插入@sometable值（2,1,1）插入@sometable值（2,1,10）插入@sometable值（2,1,1）插入@sometable值（2,2,1）插入@sometable值（3,4,25）插入@sometable值（3,5,1）插入@sometable值（3,1,1）插入@sometable值（3,1,1）插入@sometable值（3,1,1）插入@sometable值（3,1,1）插入@sometable值（ 3,1,1）

-- temp table for initial aggregation declare @t2 table (foo int, bar int, sums int) insert into @t2 select foo, bar, sum(value) from @sometable group by foo, bar

- 用于初始聚合的临时表声明@t2表（foo int，bar int，sums int）插入@ t2选择foo，bar，sum（value）来自@sometable group by foo，bar

select * from ( SELECT foo, bar, sums, ROW_NUMBER() OVER (PARTITION BY Foo ORDER BY Sums DESC) ROWNO FROM @t2) x where x.ROWNO = 1

select * from（SELECT foo，bar，sums，ROW_NUMBER（）OVER（PAROTION BY Foo ORDER BY Sums DESC）ROWNO from @ t2）x其中x.ROWNO = 1