Results 1 to 3 of 3
Thread: Finding the most count
Nov 5, 2008, 21:33 #1
- Join Date
- Apr 2003
- daejeon, South Korea
- 1 Post(s)
- 0 Thread(s)
Finding the most countCode:
data in myTable5 (id) continent city (1) 2 Rome (2) 1 Peking (3) 1 Seoul (4) 2 Paris (5) 1 Tokyo (6) 3 New York (7) 2 Berlin (8) 3 Montreal (9) 2 London
I like to find which continent is most frequently in myTable5.
With the data above, (2) is the answer.
I made the following trial code for finding the answer.
trial code <cfloop from='1' to='3' index='i'> <cfquery datasource='db1' name='test#i#'> select count(*) as count from myTable5 where continent=#i# </cfquery> </cfloop> <cfif ( test1.count is test2.count or test1.count gt test2.count) and (test1.count is test3.count or test1.count gt test3.count)> 1 <cfelseif test2.count gt test1.count and (test2.count is test3.count or test2.count gt test3.count)> 2 <cfelseif test3.count gt test1.count and test3.count gt test1.count> 3 </cfif> result 2
If other continents(like Africa, Oceania, South America, and so forth) is added into myTable5, the code will be too long and complex to understand.
Do you have any simple code, especially in database way, for finding the answer 2 with the data in myTable5?