Prev: HELP WITH SELECT STATEMENT PLEASE
Next: sp_prepexec syscomments?
From: Mario Blataric on 17 Apr 2008 16:28
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.
From: Plamen Ratchev on 17 Apr 2008 17:17
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

From: Mario Blataric on 18 Apr 2008 02:15
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.
From: Plamen Ratchev on 18 Apr 2008 08:42
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

From: --CELKO-- on 18 Apr 2008 10:30
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.
 |  Next  |  Last
Pages: 1 2
Prev: HELP WITH SELECT STATEMENT PLEASE
Next: sp_prepexec syscomments?