RE: Using Max Function on more that two data values
Neah... it's overrated. ..:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com -Original Message- From: Raymond Camden [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2008 11:45 AM To: CF-Talk Subject: Re: Using Max Function on more that two data values Well, I guess if you want to _read_ and all. ;) On Wed, Sep 24, 2008 at 10:17 AM, s. isaac dealey [EMAIL PROTECTED] wrote: Actually MAX works in QofQ. I'd use that instead of maxrows: cfquery name=foo dbtype=query select max(something) as maxvalue from somequery /cfquery Except that they wanted the max count and the name, not just the max -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 781.769.0723 http://onTap.riaforge.org/blog ~| 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:313047 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Using Max Function on more that two data values
Actually MAX works in QofQ. I'd use that instead of maxrows: cfquery name=foo dbtype=query select max(something) as maxvalue from somequery /cfquery On Tue, Sep 23, 2008 at 6:14 PM, s. isaac dealey [EMAIL PROTECTED] wrote: cfquery name=PartyConstituencyResults datasource=#request.datasource# SELECT c.ConstituencyName, s.ConstituencyNo, v.Party, Sum(v.Votes) AS TotalVotes From (Constituency c INNER JOIN PollingStation s ON c.ConstituencyNo = s.ConstituencyNo) INNER JOIN VoteResults v ON s.AgentPhoneNo = v.AgentPhoneNo WHERE v.ResultCategory = 'L' AND s.ConstituencyNo IN (Select ConstituencyNo From Constituency) Group By c.ConstituencyName, s.ConstituencyNo, v.Party Order By c.ConstituencyName /cfquery After this query, add a query of query that gets the top 1 record sorted by totalVotes desc: cfquery name=WinningConstituency dbtype=query maxrows=1 select * from PartyConstituencyResults order by TotalVotes desc /cfquery That oughta do it. :) hth -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 781.769.0723 http://onTap.riaforge.org/blog ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:313017 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Using Max Function on more that two data values
Actually MAX works in QofQ. I'd use that instead of maxrows: cfquery name=foo dbtype=query select max(something) as maxvalue from somequery /cfquery Except that they wanted the max count and the name, not just the max -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 781.769.0723 http://onTap.riaforge.org/blog ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:313019 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Using Max Function on more that two data values
Well, I guess if you want to _read_ and all. ;) On Wed, Sep 24, 2008 at 10:17 AM, s. isaac dealey [EMAIL PROTECTED] wrote: Actually MAX works in QofQ. I'd use that instead of maxrows: cfquery name=foo dbtype=query select max(something) as maxvalue from somequery /cfquery Except that they wanted the max count and the name, not just the max -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 781.769.0723 http://onTap.riaforge.org/blog ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:313020 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Using Max Function on more that two data values
Well, I guess if you want to _read_ and all. ;) It's easier to find the time to read when you've got a smaller number of open source project emails to respond to. ;) -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 781.769.0723 http://onTap.riaforge.org/blog ~| 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:313021 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Using Max Function on more that two data values
Hi S. Isaac, this worked fine after some tweeking. Currently my first query returns 24 rows of records (because there are currently for constituencies returning results and each has 6 parties participating), but the logic beats me, how is it that your cfquery name=PartyConstituencyResults datasource=#request.datasource# After this query, add a query of query that gets the top 1 record sorted by totalVotes desc: cfquery name=WinningConstituency dbtype=query maxrows=1 select * from PartyConstituencyResults order by TotalVotes desc /cfquery That oughta do it. :) hth -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 781.769.0723 http://onTap.riaforge.org/blog ~| 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:313030 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Using Max Function on more that two data values
Sorry for the premature posting I mistakenly hit the enter key. Now to my question Hi S. Isaac, this worked fine after some tweeking. Currently my first query returns 24 rows of records (because there are currently for constituencies returning results and each has 6 parties participating)so I had to change the Maxrows to 4 to get the desired result. However the logic beats me, how is it that your SQL returns only the maximum on each row? I will appreciate some the lesson. Thanks. NB: Thanks to Raymond as well I learnt some more about Q of Q's Well, I guess if you want to _read_ and all. ;) It's easier to find the time to read when you've got a smaller number of open source project emails to respond to. ;) -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 781.769.0723 http://onTap.riaforge.org/blog ~| 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:313031 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Using Max Function on more that two data values
He added the order by desc, so that it sorted the highest to lowest.. Had it been asc, you would have gotten the four smallest returns. Rob On Wed, Sep 24, 2008 at 4:52 PM, Fawzi Amadu [EMAIL PROTECTED] wrote: Sorry for the premature posting I mistakenly hit the enter key. Now to my question Hi S. Isaac, this worked fine after some tweeking. Currently my first query returns 24 rows of records (because there are currently for constituencies returning results and each has 6 parties participating)so I had to change the Maxrows to 4 to get the desired result. However the logic beats me, how is it that your SQL returns only the maximum on each row? I will appreciate some the lesson. Thanks. NB: Thanks to Raymond as well I learnt some more about Q of Q's Well, I guess if you want to _read_ and all. ;) It's easier to find the time to read when you've got a smaller number of open source project emails to respond to. ;) -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 781.769.0723 http://onTap.riaforge.org/blog ~| 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:313032 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Using Max Function on more that two data values
He added the order by desc, so that it sorted the highest to lowest.. Had it been asc, you would have gotten the four smallest returns. Parkhill makes the layup! :) He's right the order by TotalVotes desc in the query sorted the results highest to lowest and then maxrows=1 just cut it down to the records you want off the top. -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 781.769.0723 http://onTap.riaforge.org/blog ~| 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:313033 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Using Max Function on more that two data values
cfquery name=PartyConstituencyResults datasource=#request.datasource# SELECT c.ConstituencyName, s.ConstituencyNo, v.Party, Sum(v.Votes) AS TotalVotes From (Constituency c INNER JOIN PollingStation s ON c.ConstituencyNo = s.ConstituencyNo) INNER JOIN VoteResults v ON s.AgentPhoneNo = v.AgentPhoneNo WHERE v.ResultCategory = 'L' AND s.ConstituencyNo IN (Select ConstituencyNo From Constituency) Group By c.ConstituencyName, s.ConstituencyNo, v.Party Order By c.ConstituencyName /cfquery After this query, add a query of query that gets the top 1 record sorted by totalVotes desc: cfquery name=WinningConstituency dbtype=query maxrows=1 select * from PartyConstituencyResults order by TotalVotes desc /cfquery That oughta do it. :) hth -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 781.769.0723 http://onTap.riaforge.org/blog ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:313001 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
