449,284 Members | 1,237 Online Need help? Post your question and get tips & solutions from a community of 449,284 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
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"

 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" If I understood you properly DECLARE @MaxWidth IntegerSET @MaxWidth = 240SELECT [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" If I understood you properly DECLARE @MaxWidth IntegerSET @MaxWidth = 240SELECT [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" 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 timesimage4 could be 250 - (550+250=800) Failcount = 2, try again 3 more timesimage5 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 timesimage6 could be 75 - (600+500=1100) Failcount = 4, try again 1 more timesimage6 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. 