SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Most common MySQL value in table

    I'm looking for a way to determine the most common value in a column, is this possible? I've been looking around the MySQL and PHP documentation to no avail, and Google searching seems to show up only the odd very convoluted and messy way of doing this.

    Basically, say we have a table of names:

    id, name
    1, Danny
    2, Sam
    3, Kerry
    4, Danny
    5, Paul
    6, Sam
    7, Kerry
    8, Danny
    9, Jack
    10, Hudson

    ...and we want to determine the most popular, second most popular, etc. In this example, the most popular name is Danny (3 rows), the second most is tied between Sam and Kerry (2 rows each), then Jack, Hudson and Paul only have 1 row each.

    In the case of a tie, I would simply sort the two rows alphabetically. The resulting popularity ranking would be:

    1) Danny (3 results)
    2) Kerry (2 results)
    3) Sam (2 results)
    4) Hudson (1 result)
    5) Jack (1 result)
    6) Paul (1 result)

    Is there any way I can do this with MySQL? Any examples or links are appreciated, thanks! Rich

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select name
         , count(*) as howmany
      from daTable
    group
        by name
    order
        by howmany desc
         , name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Awesome, thanks!
    Last edited by richardhenry; Mar 16, 2008 at 10:32.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you run it just like any other query. have you ever run a query in a program before?

  5. #5
    SitePoint Member Tybe-O's Avatar
    Join Date
    Mar 2008
    Location
    NL, Rotterdam
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just to get ahead of problems:

    This might get pretty heavy if the list of user-names gets larger and larger. If you start noticing bad performance you might consider performing this counting calculation only when a new name is added and then store those results in an easy-to-query table....

  6. #6
    SitePoint Zealot
    Join Date
    Sep 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sure thing. I've setup a cron job to run this query every ten minutes, which then updates a cache file (which is the actual output on the user-end).

    Thanks again for everyones help.


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
  •