sqlsql-serversql-server-ce

How do I select the first row per group in an SQL Query?

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

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

How do I do that?

Solution 
  • 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