SELECT UNTIL ?

[Douglas]

Hi

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%^T RHT
2 60 O*&^G&*^OGBV&VB ^^&
3 120 YE%$CC%$EYYEC%$ C%$
4 200 %E$Y^HYS^YHU%^T RHT
5 60 O*&^G&*^OGBV&VB ^^&
6 120 YE%$CC%$EYYEC%$ C%$
------------------------------------------

Dim CurrentTotalWid th As Integer = 0
Dim MaxWidth As Integer = 240
Dim MaxWidthExceede d As Integer = 0

Do

If CurrentTotalWid th MaxWidth Then
MaxWidthExceede d &= MaxWidthExceede d + 1
End If

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

CurrentTotalWid th = CurrentTotalWid th + [ImageWidth]

Loop Until MaxWidthExceede d = 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

[Uri Dimant]

Douglas

If I understood you properly

DECLARE @MaxWidth Integer
SET @MaxWidth = 240

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

"Douglas" <po*****@the.gr oup.so.everyone .can.learnwrote in message news:11******** ******@angel.am net.net.au...
Hi

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%^T RHT
2 60 O*&^G&*^OGBV&VB ^^&
3 120 YE%$CC%$EYYEC%$ C%$
4 200 %E$Y^HYS^YHU%^T RHT
5 60 O*&^G&*^OGBV&VB ^^&
6 120 YE%$CC%$EYYEC%$ C%$
------------------------------------------

Dim CurrentTotalWid th As Integer = 0
Dim MaxWidth As Integer = 240
Dim MaxWidthExceede d As Integer = 0

Do

If CurrentTotalWid th MaxWidth Then
MaxWidthExceede d &= MaxWidthExceede d + 1
End If

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

CurrentTotalWid th = CurrentTotalWid th + [ImageWidth]

Loop Until MaxWidthExceede d = 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

[Ed Murphy]

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=4 40.

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).

[Uri Dimant]

Ed
I think you are right
"Ed Murphy" <em*******@soca l.rr.comwrote in message
news:KX******** **********@torn ado.socal.rr.co m...

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=4 40.

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).

[Douglas]

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

some notes below to clarify...
--------------------------------------------------------------------------------

"Ed Murphy" <em*******@soca l.rr.comwrote in message news:KX******** **********@torn ado.socal.rr.co m...

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.

--------------------------------------------------------------------------------
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=600

image1 could be 500 - running total=(500)
image2 could be 50 - (500+50=550, no fail)
image3 could be 75 - (550+75=625) Failcount = 1, try again 4 more times
image4 could be 250 - (550+250=800) Failcount = 2, try again 3 more times
image5 could be 50 - (550+50=600, no fail (600 is not 600 ;))
image6 could be 75 - (600+75=675) Failcount = 3, try again 2 more times
image6 could be 75 - (600+500=1100) Failcount = 4, try again 1 more times
image6 could be 75 - (600+250=850) Failcount = 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 ;)
--------------------------------------------------------------------------------

(Insert standard Celko "rows vs. records" rant here.)

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

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=4 40.

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.

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

After that, give up, to conserve server resources.


--------------------------------------------------------------------------------

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).

--------------------------------------------------------------------------------
i'm looking into that...

once again,
thanks for your interest
Douglas

[Ed Murphy]

Douglas wrote:

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=600

image1 could be 500 - running total=(500)
image2 could be 50 - (500+50=550, no fail)
image3 could be 75 - (550+75=625) Failcount = 1, try again 4 more times
image4 could be 250 - (550+250=800) Failcount = 2, try again 3 more times
image5 could be 50 - (550+50=600, no fail (600 is not 600 ;))
image6 could be 75 - (600+75=675) Failcount = 3, try again 2 more times
image6 could be 75 - (600+500=1100) Failcount = 4, try again 1 more times
image6 could be 75 - (600+250=850) Failcount = 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 ;)

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

(Insert standard Celko "rows vs. records" rant here.)

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

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.

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

i'm looking into that...

Yeah, I think it'll adapt well enough to what you described above.

[Douglas]

Hey guys,

I guess the key question for me is, can this be done entirely in SQL?

importantly, is the SQL WHILE loop suitable ? using a while command *seems* to me to be used for executing a "whole" query as part of a logic branch, not "keep picking out records while condition = true/false"

my SQL is reasonable, for most general things, but im not so sharp with the more advanced concepts

maybe i will end up having to write an asp.net.vb function that grabs a bunch of records 5 at a time and handles the logic processing side of things... [just thinking out loud]
Cheers,
Douglas

--------------------------------------------------------------------------------

"Ed Murphy" <em*******@soca l.rr.comwrote in message news:xe******** ***********@tor nado.socal.rr.c om...

Douglas wrote:

>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=600

image1 could be 500 - running total=(500)
image2 could be 50 - (500+50=550, no fail)
image3 could be 75 - (550+75=625) Failcount = 1, try again 4 more times
image4 could be 250 - (550+250=800) Failcount = 2, try again 3 more times
image5 could be 50 - (550+50=600, no fail (600 is not 600 ;))
image6 could be 75 - (600+75=675) Failcount = 3, try again 2 more times
image6 could be 75 - (600+500=1100) Failcount = 4, try again 1 more times
image6 could be 75 - (600+250=850) Failcount = 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 ;)

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

--------------------------------------------------------------------------------
aww shucks
--------------------------------------------------------------------------------

> (Insert standard Celko "rows vs. records" rant here.)

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

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).

--------------------------------------------------------------------------------
oh yes....

i rememeber them days... its all coming back to me now hehe
--------------------------------------------------------------------------------

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

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

i'm looking into that...

Yeah, I think it'll adapt well enough to what you described above.

[Alex Kuznetsov]

Douglas,

For a limited number of images you can select all possible combinations
as follows:

DECLARE @n INT
SET @n = 5

SELECT P.Number AS Combination, N.Number
FROM
--- Numbers is a table with numbers from 0 to 9999
(SELECT Number FROM Numbers WHERE Number BETWEEN 0 AND (@n-1)) N
JOIN
(SELECT Number FROM Numbers WHERE Number BETWEEN 1 AND POWER(2,@n)) P
ON P.Number & POWER(2,N.Numbe r) 0
ORDER BY P.Number

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

[Ed Murphy]

Douglas wrote:

I guess the key question for me is, can this be done */entirely/* in SQL?

importantly, is the SQL WHILE loop suitable ? using a while command
*seems* to me to be used for executing a "*/whole/*" query as part of a
logic branch, not "keep picking out records while condition = true/false"

Any of the previously cited examples (all of which are done entirely in
SQL) ought to be adaptable. The WHILE loop allows you to iterate over a
cursor, which lets you go through a table one row at a time. (You do
have to break out of the loop if you run out of rows.)

[Erland Sommarskog]

Douglas (po*****@the.gr oup.so.everyone .can.learn) writes:

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 :(

It's probably easiest to use a temp table/table variable for this:

DECLARE @tbl TABLE (imageid int NOT NULL PRIMARY KEY,
width int NOT NULL,
rowno int NOT NULL,
accum_width int NULL)

INSERT @tbl (imageid, width, rowno)
SELECT TOP (@ImageQty)
imageid, width, row_number(ORDE R BY width, newid())
FROM tblImages
ORDER BY newid()

UPDATE @tbl
SET accum_width = (SELECT SUM(b.width)
FROM @tbl b
WHERE b.rowno <= a.rowno)
FROM @tbl a

DELETE @tbl WHERE accum_width 600

Note: this solution presumes SQL 2005.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx