You can also just check the value using CFOUTPUT and then add it to the list if it is less than six.
<cfoutput query="SockQuery"> <cfset SockList = ""> <cfif NumberOfSock lt 6> <cfset SockList = ListAppend(SockList, SockDrawerNumber)> </cfif> </cfoutput> Peter Bagnato -----Original Message----- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 13, 2003 7:37 PM To: CF-Talk Subject: Re: SQL Help Requested: Unique Same Field Sum Combinations Thomas Sammons wrote: > > How would you do this in CF (any version 4.5 or higher): return all > unique combinations of rows where the unique row field value > combination has a certain summed value? > > For example: > I have a SocksTable with 6 SockDrawers, and each SockDrawer has a > number of Socks in it. I want to get the list of unique combinations > of SockDrawers that have a combined total of, say, 6 Socks or less. I don't think SQL is the right tool for it, although if you dbms supports WITH RECURSIVE it might be an option. I think I would use CF to loop over an array for this. Code below, but just by hand so you probably need to debug it. Mind the wrap. <cfscript> maximum = 6; socksArray = ArrayNew(1); socksArray[1]["idlist"] = 1; socksArray[1]["socktotal"] = 5; socksArray[2]["idlist"] = 2; socksArray[2]["socktotal"] = 2; socksArray[3]["idlist"] = 3; socksArray[3]["socktotal"] = 1; socksArray[4]["idlist"] = 4; socksArray[4]["socktotal"] = 3; socksArray[5]["idlist"] = 5; socksArray[5]["socktotal"] = 4; i = 1; while (i lt ArrayLen(socksArray)) { j = i + 1; while (j lt ArrayLen(socksArray)) { if (socksArray[i]["sockTotal"]+socksArray[j]["sockTotal"] LTE maximum and not REFind("," & socksArray[i]["sockTotal" & "," & socksArray[j]["sockTotal"],",([0-9]*),.*\1")) { ArrayAppend(socksArray(StructNew()); socksArray[ArrayLen(socksArray)]["idlist"] = socksArray[i]["idlist"] & "," socksArray[j]["idlist"]; socksArray[ArrayLen(socksArray)]["socktotal"] = socksArray[i]["sockTotal"] + socksArray[j]["sockTotal"]; } j = j + 1; } i = i + 1; } </cfscript> Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
