By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,833 Members | 2,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,833 IT Pros & Developers. It's quick & easy.

SELECT UNTIL ?

P: n/a
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%^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 = 0
Dim MaxWidth As Integer = 240
Dim MaxWidthExceeded As Integer = 0

Do

If CurrentTotalWidth MaxWidth Then
MaxWidthExceeded &= MaxWidthExceeded + 1
End If

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

CurrentTotalWidth = CurrentTotalWidth + [ImageWidth]

Loop Until MaxWidthExceeded = 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
Nov 30 '06 #1
Share this Question
Share on Google+
11 Replies


P: n/a
Douglas

If I understood you properly

DECLARE @MaxWidth Integer
SET @MaxWidth = 240

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

"Douglas" <po*****@the.group.so.everyone.can.learnwrote in message news:11**************@angel.amnet.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%^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 = 0
Dim MaxWidth As Integer = 240
Dim MaxWidthExceeded As Integer = 0

Do

If CurrentTotalWidth MaxWidth Then
MaxWidthExceeded &= MaxWidthExceeded + 1
End If

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

CurrentTotalWidth = CurrentTotalWidth + [ImageWidth]

Loop Until MaxWidthExceeded = 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
Nov 30 '06 #2

P: n/a
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).
Nov 30 '06 #3

P: n/a
Ed
I think you are right
"Ed Murphy" <em*******@socal.rr.comwrote in message
news:KX******************@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).

Nov 30 '06 #4

P: n/a

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

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

"Ed Murphy" <em*******@socal.rr.comwrote in message news:KX******************@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.
--------------------------------------------------------------------------------
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=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.
--------------------------------------------------------------------------------
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

Nov 30 '06 #5

P: n/a
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.
Nov 30 '06 #6

P: n/a
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*******@socal.rr.comwrote in message news:xe*******************@tornado.socal.rr.com...
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.
Nov 30 '06 #7

P: n/a
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.Number) 0
ORDER BY P.Number

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

Nov 30 '06 #8

P: n/a
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.)
Nov 30 '06 #9

P: n/a
Douglas (po*****@the.group.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(ORDER 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****@sommarskog.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
Nov 30 '06 #10

P: n/a
>I guess the key question for me is, can this be done entirely in SQL? <<

The answer is always "Yes, we can do it in SQL!"

The right answer is "But, like a size 26 thong, just because you can
does not mean you should!"

Google around for "Bin packing" and/or "Knapsack" problems on some math
websites. This is a known NP-complete problem. In English, it means
that the only way to solve it is to try all possible combinations, so
the execution time grows fastrer than any polynominal expression (i.e
think about factorials or worse).

There are often several valid solutions, too. Being a set-oriented
language, SQL will attempt to find the set of ALL solutions. And run
forever.

This is a job for a procedure (yes, Celko is saying nice thing about
procedural code!) which will stop at the first usable answer, even if
it is not optimal. Now you have to pick your algorithm. This is
usually the Greedy algorithm ("grab the biggest bite you can and add it
to the answer; see if you met the goal; if not, repeat") modified to do
some back tracking.

Dec 1 '06 #11

P: n/a
--CELKO-- wrote:
Google around for "Bin packing" and/or "Knapsack" problems on some math
websites. This is a known NP-complete problem. In English, it means
that the only way to solve it is to try all possible combinations, so
the execution time grows fastrer than any polynominal expression (i.e
think about factorials or worse).

There are often several valid solutions, too. Being a set-oriented
language, SQL will attempt to find the set of ALL solutions. And run
forever.

This is a job for a procedure (yes, Celko is saying nice thing about
procedural code!) which will stop at the first usable answer, even if
it is not optimal. Now you have to pick your algorithm. This is
usually the Greedy algorithm ("grab the biggest bite you can and add it
to the answer; see if you met the goal; if not, repeat") modified to do
some back tracking.
As I understood it, the original poster already had such an algorithm
in mind: "sort the images in random order, then try grabbing them one
at a time, until you've failed five times". (Or fallen off the end of
the list.)
Dec 2 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.