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

Question

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?

Answer 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

Answer 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.

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).

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

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

"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."

hth

Answer 3

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

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

Answer 4

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

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)

Answer 5

(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

Answer 6

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.

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" :

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))

Answer 7

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

-- 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)

Answer 8

SQL Server 2005 you can use this:

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

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