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

admin

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

sql

我有这个SQL查询：

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

这将导致类似于以下的输出：

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

我想每个Foo仅保留第一行，而忽略其余的行。

47  1   100
46  0   100
44  0   2

我怎么做？

阅读 118

2021-05-10

共1个答案

admin

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

2021-05-10