Get most common data in single column (mySQL) and echo them?

Discussion in 'PHP' started by Miquexia, Apr 19, 2010.

0
  1. #1
    Hello,

    How can I get the most common data in a single database column and echo them?

    Because I'd like to make a top 30 of cities on my website.

    For example, there's a column called "Cities". I'd like to count all the rows and check which ones are the most common. Like Amsterdam, or anything else.

    Amsterdam has 5 rows (Top 1), Rotterdam has 4 rows (Top 2), etc.

    How can I do this?

    Thanks.
     
    Miquexia, Apr 19, 2010 IP
  2. lukeg32

    lukeg32 Peon

    Messages:
    645
    Likes Received:
    19
    Best Answers:
    1
    Trophy Points:
    0
    #2
    It should be quite straight forward really;

    SELECT count(cities) as city_count, cities FROM YOURTABLE GROUP BY cities ORDER BY city_count DESC LIMIT 30;
    Code (markup):
     
    Last edited: Apr 19, 2010
    lukeg32, Apr 19, 2010 IP