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

[--CELKO--]

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

[Ed Murphy]

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