Row-Limit per Group

damava · #1 (**permalink**) 06-16-06, 09:08

I need to sum() the 3 biggest values from a specific field of each group.

Using

Code:

SELECT sum(numbers) FROM table GROUP BY field

operates on every row and LIMIT only restricts the final result. What I need is a way to limit the rows per group.

r937 · #2 (**permalink**) 06-16-06, 10:11

Code:

select sum(numbers)
  from daTable as X   
 where ( select count(*) 
           from daTable 
          where numbers > X.numbers) < 3

Peter.Vanroose · #3 (**permalink**) 06-17-06, 17:30

This only gives a single amount, i.e., the sum of the biggest three from the whole table.
To obtain the sums of the three biggest values from each group:

Code:

SELECT field, sum(numbers)
FROM   daTable as X
WHERE  (SELECT count(*)
        FROM   daTable
        WHERE  field = X.field AND numbers > X.numbers) < 3
GROUP BY field

r937 · #4 (**permalink**) 06-17-06, 18:00

well spotted, peter, you are quite right, i misunderstood the question

Internet Services

Web Development

Digital Marketing

Consumer Tech