如何在 SQL 查询中选择每组的第一行? [英] How do I select the first row per group in an SQL Query?

问题描述

我有这个 SQL 查询:

I've got this SQL query:

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

我只想每个 Foo 的第一行(它及其最高的 Bar)，而忽略其余部分.

I'd like to have only the first row per Foo (it and its highest Bar), and ignore the rest.

47  1   100
46  0   100
44  0   2

我该怎么做?

推荐答案

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

这篇关于如何在 SQL 查询中选择每组的第一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！