find first n records that adds up to certain amount

[aurora00]

Let's said I have a table with an id column and an amount column. I want to select the first n records that adds up to certain amount, say 100. Something similar to the SQL below:

select id, amount from table order by id limit sum(amount) < = 100

Anyway to do it with SQL?

 

[mrdenny]

Here is what you are looking for. (It works in MS SQL, I'm not sure what platform you are using.)

select t1.id, amount
from table t1
join (select id, sum(amount) amt from table group by id having sum(amount) >= 100) t2 on t1.id = t2.id
order by t1.id

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[PHV]

SELECT A.ID, A.Amount
FROM yourTable A INNER JOIN yourTable B ON A.ID >= B.ID
GROUP BY A.ID, A.Amount
HAVING Sum(B.Amount) <= 100
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[aurora00]

Thank you for the quick response! I use postgre and it works! Unfortunately the performance is also very slow. I wonder if the inner join causes some kind of n^2 behavior.

 

[r937]

yeah, probably n^2

but if you wanted n^1, just cursor the table and find the "first n records" while scanning



r937.com | rudy.ca