Finding the most count
I have myTable5 like the bove.
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.
The trial code above works fine, but it's not flexible.
<cfloop from='1' to='3' index='i'>
<cfquery datasource='db1' name='test#i#'>
select count(*) as count
<cfif ( test1.count is test2.count or test1.count gt test2.count) and (test1.count is test3.count or test1.count gt test3.count)>
<cfelseif test2.count gt test1.count and (test2.count is test3.count or test2.count gt test3.count)>
<cfelseif test3.count gt test1.count and test3.count gt test1.count>
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?
<cfquery datasource='db1' name='test'>
select count(continent) as count,continent
group by continent
order by count desc
I guess I found it in database way like the above.
I was gonna say, lol. This had nothing to do with CF, and was a SQL question.