Posted by Uri Dimant on 11/30/06 10:12

Ed
I think you are right


"Ed Murphy" <emurphy42@socal.rr.com> wrote in message
news:KXxbh.35291$Fg.20663@tornado.socal.rr.com...
> 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]