sql server (alternate): Hi =20

Im not an SQL n00b, but I'm no SQL guru...

I'd like to work out the SQL code required to achieve the following goal =
in pseudocode:

Grap a record
Using info from that record, add (the width) to a running total.
When the running total exceeds a certain value, stop grabbing records.

Here's a snippet of code to demonstrate the functionality that is =
required:


-------------------------------------------------------------------------=
-------

ImageID ImageWidth ImageBLOB
------------------------------------------
1 60 %E$Y^HYS^YHU%^TRHT
2 60 O*&^G&*^OGBV&VB^^&
3 120 YE%$CC%$EYYEC%$C%$
4 200 %E$Y^HYS^YHU%^TRHT
5 60 O*&^G&*^OGBV&VB^^&
6 120 YE%$CC%$EYYEC%$C%$
------------------------------------------

Dim CurrentTotalWidth As Integer =3D 0
Dim MaxWidth As Integer =3D 240
Dim MaxWidthExceeded As Integer =3D 0

Do

If CurrentTotalWidth > MaxWidth Then=20
MaxWidthExceeded &=3D MaxWidthExceeded + 1 =20
End If

SELECT [ImageID], [ImageWidth], [ImageBLOB], FROM [tblImages]

CurrentTotalWidth =3D CurrentTotalWidth + [ImageWidth]

Loop Until MaxWidthExceeded =3D 5

-------------------------------------------------------------------------=
-------



I don't assume that the code I have submitted is the most efficient =
yaddah yaddah yaddah, I'm open to suggestions to make things =
efficient... maybe a SELECT TOP 10 ... so that im running one query =
instead of ten ? etc etc

Anyways,
Thanks in advance,

Douglas

If I understood you properly=20

DECLARE @MaxWidth Integer=20
SET @MaxWidth =3D 240

SELECT [ImageID], [ImageWidth], [ImageBLOB], FROM [tblImages]
WHERE [ImageWidth]<@MaxWidth=20

[quoted text, click to view]
Hi =20

Im not an SQL n00b, but I'm no SQL guru...

I'd like to work out the SQL code required to achieve the following =
goal in pseudocode:

Grap a record
Using info from that record, add (the width) to a running total.
When the running total exceeds a certain value, stop grabbing records.

Here's a snippet of code to demonstrate the functionality that is =
required:


-------------------------------------------------------------------------=
-----

ImageID ImageWidth ImageBLOB
------------------------------------------
1 60 %E$Y^HYS^YHU%^TRHT
2 60 O*&^G&*^OGBV&VB^^&
3 120 YE%$CC%$EYYEC%$C%$
4 200 %E$Y^HYS^YHU%^TRHT
5 60 O*&^G&*^OGBV&VB^^&
6 120 YE%$CC%$EYYEC%$C%$
------------------------------------------

Dim CurrentTotalWidth As Integer =3D 0
Dim MaxWidth As Integer =3D 240
Dim MaxWidthExceeded As Integer =3D 0

Do

If CurrentTotalWidth > MaxWidth Then=20
MaxWidthExceeded &=3D MaxWidthExceeded + 1 =20
End If

SELECT [ImageID], [ImageWidth], [ImageBLOB], FROM [tblImages]

CurrentTotalWidth =3D CurrentTotalWidth + [ImageWidth]

Loop Until MaxWidthExceeded =3D 5

-------------------------------------------------------------------------=
-----



I don't assume that the code I have submitted is the most efficient =
yaddah yaddah yaddah, I'm open to suggestions to make things =
efficient... maybe a SELECT TOP 10 ... so that im running one query =
instead of ten ? etc etc

Anyways,
Thanks in advance,

[quoted text, click to view]

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,

Ed
I think you are right


[quoted text, click to view]

Hi guys, and thanks for your interest in this puzzle :)

some notes below to clarify...


-------------------------------------------------------------------------=
-------

[quoted text, click to view]

-------------------------------------------------------------------------=
-------


ok, I thought id try and strip it down to its bare essentials, but i =
perhaps should have mentioned...

I am SELECTing * FROM (SELECT TOP @ImageQty FROM tblImages ORDER BY =
NEWID() ) AS [ImageTable] ORDER BY [ImageWidth];

So i can ask for 5 images chosen at random and sorted by width.

The trouble is, that some images are panoramic, and up to 9 times wider =
than standard pics. Other shots are portait, and therefore a lot =
narrower. This causes display anomolies in the resulting html :(

if maxwidth=3D600

image1 could be 500 - running total=3D(500)
image2 could be 50 - (500+50=3D550, no fail)
image3 could be 75 - (550+75=3D625) Failcount =3D 1, try again 4 more =
times
image4 could be 250 - (550+250=3D800) Failcount =3D 2, try again 3 more =
times
image5 could be 50 - (550+50=3D600, no fail (600 is not > 600 ;))
image6 could be 75 - (600+75=3D675) Failcount =3D 3, try again 2 more =
times
image6 could be 75 - (600+500=3D1100) Failcount =3D 4, try again 1 more =
times
image6 could be 75 - (600+250=3D850) Failcount =3D 5, stop.

So, requesting a FINITE qty of images will not meet the needs of the =
goal.

The goal is: to get as many pictures, as close to a total width of 600 =
as possible. (without causing server meltdown, of course ;)


-------------------------------------------------------------------------=
-------

[quoted text, click to view]

-------------------------------------------------------------------------=
-------


I'd like to know more about that... although, now that I have asked, I =
may live to regret it.


-------------------------------------------------------------------------=
-------

[quoted text, click to view]

-------------------------------------------------------------------------=
-------


Get five more, each time checking to see if we can squeeze it in to the =
remaining value.=20

After that, give up, to conserve server resources.

=20
-------------------------------------------------------------------------=
-------
[quoted text, click to view]

-------------------------------------------------------------------------=
-------


i'm looking into that...

once again,
thanks for your interest

[quoted text, click to view]

Ah, much clearer than the original spec, thank you.

[quoted text, click to view]

This pertains to all the people accustomed to e.g. Access, where
each table's records have row numbers, and default to sorting in
that order. Then they encounter any number of things in SQL that
look deceptively similar: primary keys, auto-assigned ID numbers,
or just a query without an ORDER BY that happens (this time!) to
return data in the same order it was inserted. Not to mention
views and ORDER BY (see http://www.devx.com/dbzone/Article/8048).

There's a parallel "columns vs. fields" rant - I think it pertains
to inappropriate data types and/or lack of proper check constraints.

[quoted text, click to view]