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 and ignore the rest.

47  1 100
46  0 100
44  0 2

How do I do that?

Answer 1

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 2

(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 3

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 4

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

Answer 5

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 6

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)