Get rows whose sum matches a value

This should be simple, but I can't work it out without cursors.

I have a table with quantity field. I want to get all first rows
whose sum of quantity matches defined value.

No Quantity
=============
1 50
2 50
3 80
4 80
5 80
6 50

If defined value is, lets say, 180, query should return rows 1..3
Something like
select *
from table
where sum(quantity) = 180
order by No

Any way to work this out?

Thanks,

Mario B.

Re: Get rows whose sum matches a value

You can accomplish that query without using a cursor. However, performance
will be awful on a large data set.

CREATE TABLE Foo (
nbr INT PRIMARY KEY,
quantity INT);

INSERT INTO Foo VALUES(1, 50);
INSERT INTO Foo VALUES(2, 50);
INSERT INTO Foo VALUES(3, 80);
INSERT INTO Foo VALUES(4, 80);
INSERT INTO Foo VALUES(5, 80);
INSERT INTO Foo VALUES(6, 50);

DECLARE @limit INT;

SET @limit = 180;

SELECT T.nbr, T.quantity, T.running_total
FROM (SELECT A.nbr, A.quantity, COALESCE(SUM(B.quantity), 0)
FROM Foo AS A
LEFT OUTER JOIN Foo AS B
ON A.nbr >= B.nbr
GROUP BY A.nbr, A.quantity
) AS T(nbr, quantity, running_total)
WHERE T.running_total <= @limit
ORDER BY T.nbr;


HTH,

Plamen Ratchev
http://www.SQLStudio.com

Re: Get rows whose sum matches a value

Thanks, would never think of that.

When you say performance will be awful, do you mean still faster or
even slower than with cursors?

Thanks,

Mario B.

Re: Get rows whose sum matches a value

With a large result set this approach will be slower than cursor. To
calculate the running total value it needs to perform (N + N^2)/2 row scans
on the table (where N is the total number of rows). On the other side a
cursor will scan a row only once.

Unfortunately even in SQL Server 2005 the OVER clause for aggregate
functions does not fully support the ANSI OLAP extensions which will allow
us to do things like SUM(quantity) OVER(ORDER BY nbr) which will be a lot
more efficient.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Re: Get rows whose sum matches a value

If you can do this on DB2 or another SQL product with full OLAP
functions instead of T-SQL, it is easy:

SELECT X.vague_nbr, X.foo_qty
FROM (SELECT vague_nbr, foo_qty, SUM(foo_qty)
OVER (ORDER BY vague_nbr
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT
ROW))
FROM Foobar
AS X(vague_nbr, foo_qty, running_tot)
WHERE X.running_tot >= 180;

Otherwise, you will have to a self-join that gets to be exponentially
awful as the size of the table increases.

Re: Get rows whose sum matches a value

Thanks everyone.

I settled with cursor at the end, since table has near million records
and growing and I'm working with SQL 2005, so other SQL languages are
not an option.