Hello, I have a query that returns sum of votes for each party in a constituency and I need to find out which party had the most votes within each constituency. Here is more detail about what I have done and what I want to do.
I have the following tables along with their attributes(the stared fields show the primary key: Agent(AgentPhoneNo*, NameOfAgent) Constituency(ConstituencyNo*, ConstituencyName,District,RegionCode) PollingStation(StationNo*,ConstituencyNo*,AgentPhoneNo, StationName) VoteResults(Party*, ResultsCategory*,Votes, AgentPhoneNo*) Results category has two values (S,L) and the number of parties is variable My issue is this, below is my code that returns the total votes received by each party within each constituency. This query works fine. <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> Now I want to check and return the name and total votes of the party with the maximum votes within each constituency. And but I can't seem to figure out how to do that, especially because the max() function takes only to values. I will appreciate any guidance on how to write a query that returns the the party with the maximum votes in each constituency and the votes it got. TIA ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:312993 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
