Re: Is there a non-aggregate Max() function in MySQL?
I don't have MySQL installed, but this is how I'd do it in MSSQL (and I don't see why it wouldn't work for you...): SELECT *, OrderValue = CASE WHEN MinPrice MaxPrice THEN MinPrice ELSE MaxPrice END FROM mytable ORDER BY OrderValue DESC Seb Seb Duggan Web ColdFusion Developer e: s...@sebduggan.com t: 07786 333184 w: http://sebduggan.com On 14 Dec 2008, at 03:26, Jim McAtee wrote: I have a table with two columns containing min and max values minprice INT maxprice INT The max price may not be present and is set to zero when that's the case, so data might look like: 1 5 5 0 3 10 8 0 6 0 I want to order by the larger of the two column values ORDER BY Max(minprice, maxprice) DESC But SQL's MAX() can't be used like this since it's an aggregate function. Is there a MySQL function that would do this? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316744 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Is there a non-aggregate Max() function in MySQL?
On Sat, Dec 13, 2008 at 10:26 PM, Jim McAtee jmca...@mediaodyssey.comwrote: The max price may not be present and is set to zero when that's the case, so data might look like: 1 5 5 0 3 10 8 0 6 0 I want to order by the larger of the two column values ORDER BY Max(minprice, maxprice) DESC But SQL's MAX() can't be used like this since it's an aggregate function. Is there a MySQL function that would do this? I may be misunderstanding what you expect the result to be but ORDER BY minprice desc, maxprice desc would return rows in descending order of minprice and if there were multiples of minprice, they would be ordered in descending order of maxprice. With your example date, this should be: 8 0 6 0 5 0 3 10 1 5 Stephen ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316745 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
A few unique random numbers
Man, I'm not having a good time with a seemingly simple problem, that is, to generte a bunch of (say, 6) of unique numbers out of a range. Say, the range is 20. My current code technique is mainly to use a 'pool of numbers, each less than 20', if found, try generate a number again (and here's the potential problem, that is, you don't know if the newly generated number would be a member of 'pool of numbers', so, one would have to run a check again... So, for now, the code sometime produce one or even two duplication numbers. Do you have a better way? Thanks. Don Chunshen Li ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316746 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: A few unique random numbers
numbers = {};
while (structCount(numbers) LT 6) {
numbers[randRange(1, 20)] = ;
}
numbers = structKeyList(numbers);
On Sun, Dec 14, 2008 at 3:45 PM, Don L do...@yahoo.com wrote:
Man, I'm not having a good time with a seemingly simple problem, that is, to
generte a bunch of (say, 6) of unique numbers out of a range. Say, the range
is 20.
My current code technique is mainly to use a 'pool of numbers, each less than
20', if found, try generate a number again (and here's the potential problem,
that is, you don't know if the newly generated number would be a member of
'pool of numbers', so, one would have to run a check again... So, for now,
the code sometime produce one or even two duplication numbers. Do you have a
better way?
Thanks.
Don
Chunshen Li
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316747
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: A few unique random numbers
I use this method. Originally I used this to shuffle a deck of cards. cfset ca = arrayNew(1) / cfloop from=1 to=20 index=i cfset ca[i] = i / /cfloop cfset i=1 / cfloop from=1 to=20 index=i cfset ranDom = RandRange(1,arrayLen(ca)) / cfoutput#ca[ranDom]#br //cfoutput cfset arrayDeleteAt(ca,ranDom) / /cfloop Paul. On Sun, Dec 14, 2008 at 6:45 PM, Don L do...@yahoo.com wrote: Man, I'm not having a good time with a seemingly simple problem, that is, to generte a bunch of (say, 6) of unique numbers out of a range. Say, the range is 20. My current code technique is mainly to use a 'pool of numbers, each less than 20', if found, try generate a number again (and here's the potential problem, that is, you don't know if the newly generated number would be a member of 'pool of numbers', so, one would have to run a check again... So, for now, the code sometime produce one or even two duplication numbers. Do you have a better way? Thanks. Don Chunshen Li ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316748 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: A few unique random numbers
Off topic now, but if you want to shuffle a deck of cards (or anything else), check out java.util.Collection's shuffle() method. Operates on a CF array (or any other java.util.List). cheers, barneyb On Sun, Dec 14, 2008 at 3:55 PM, Paul Kukiel pkuk...@gmail.com wrote: I use this method. Originally I used this to shuffle a deck of cards. cfset ca = arrayNew(1) / cfloop from=1 to=20 index=i cfset ca[i] = i / /cfloop cfset i=1 / cfloop from=1 to=20 index=i cfset ranDom = RandRange(1,arrayLen(ca)) / cfoutput#ca[ranDom]#br //cfoutput cfset arrayDeleteAt(ca,ranDom) / /cfloop Paul. On Sun, Dec 14, 2008 at 6:45 PM, Don L do...@yahoo.com wrote: Man, I'm not having a good time with a seemingly simple problem, that is, to generte a bunch of (say, 6) of unique numbers out of a range. Say, the range is 20. My current code technique is mainly to use a 'pool of numbers, each less than 20', if found, try generate a number again (and here's the potential problem, that is, you don't know if the newly generated number would be a member of 'pool of numbers', so, one would have to run a check again... So, for now, the code sometime produce one or even two duplication numbers. Do you have a better way? Thanks. Don Chunshen Li ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316749 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: A few unique random numbers
numbers = {};
while (structCount(numbers) LT 6) {
numbers[randRange(1, 20)] = ;
}
numbers = structKeyList(numbers);
On Sun, Dec 14, 2008 at 3:45 PM, D
Beautiful, thank you very much. Thank you too, Paul.
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316750
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: A few unique random numbers
-Original Message-
From: Barney Boisvert [mailto:bboisv...@gmail.com]
Sent: Sunday, December 14, 2008 6:51 PM
To: cf-talk
Subject: Re: A few unique random numbers
numbers = {};
while (structCount(numbers) LT 6) {
numbers[randRange(1, 20)] = ;
}
numbers = structKeyList(numbers);
I think my current job's turned my brain to mush. That said this is as
clever as hell.
Jim Davis
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316751
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: REreplace function for special characters
[q] FileName = rereplace(FileName, '(?!\.[^.]*$)\W', '', 'all') [/q] well, that is just beautiful! Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316752 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: sum of cfquery with group by
you can also do: #arraysum(queryname.columnname)# Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Tech Gate wrote: cfquery select a.Dept, c.Assonum . group by a.Dept, c.Assonum /cfquery I need to get the SUM of c.Assonum, but I can't do sum(c.Assonum ) in the sql (cfquery). What is the way to get the SUM of c.Assonum with cf function? Please advise. Thank you ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316753 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
