Posted by Ed Murphy on 11/30/06 09:59

Uri Dimant wrote:

> If I understood you properly
>
> DECLARE @MaxWidth Integer
> SET @MaxWidth = 240
> SELECT [ImageID], [ImageWidth], [ImageBLOB], FROM [tblImages]
> WHERE [ImageWidth]<@MaxWidth

That applies the limit to each row individually. He wants to apply the
limit to a running total of rows so far.

Problem: the order of selecting rows hasn't been specified. (Insert
standard Celko "rows vs. records" rant here.) If the intention is to
select rows in ORDER BY ImageID order, then the running-total decision
logic would work as follows.

1) Select ImageID 1, its ImageWidth is 60, the running total is 60.
2) Select ImageID 2, its ImageWidth is 60, the running total is
60+60=120.
3) Select ImageID 3, its ImageWidth is 120, the running total is
60+60+120=240.
4) Select ImageID 4, its ImageWidth is 200, the running total is
60+60+120+200=440.

The running total has now exceeded 240, so stop selecting rows. But
here's another problem: the description of the goal says to stop
right away, but the pseudocode says to select five more rows and
then stop.

Googling (SQL "running total") turns this up as the first hit:

http://www.sqlteam.com/item.asp?ItemID=3856

which gives three solutions, one that uses cursors and two that
don't, and says (with some annoyance) that the cursor solution
was an order of magnitude faster in speed tests (but, as usual,
involves a longer block of code).

[Back to original message]