SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,209
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Finding the most count

    Code:
    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 have myTable5 like the bove.
    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.
    Code:
    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
    The trial code above works fine, but it's not flexible.

    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?

  2. #2
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,209
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    <cfquery datasource='db1' name='test'>
    select count(continent) as count,continent
    from myTable5
    group by continent
    order by count desc
    </cfquery>
    <cfoutput>
    #test.continent#
    </cfoutput>

    I guess I found it in database way like the above.

  3. #3
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I was gonna say, lol. This had nothing to do with CF, and was a SQL question.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •