www.webdeveloper.com
Results 1 to 4 of 4

Thread: Group by sort size?

  1. #1
    Join Date
    Feb 2012
    Location
    Tallahassee, FL
    Posts
    280

    Group by sort size?

    I have a table and I want to sort by how many duplicates are in the table.
    For example,
    Table like:
    Code:
    ##   id   ##   name   ##
    #################
    ##   1   ##    Bob      ##
    ##   2   ##    Bob      ##
    ##   3   ##    Bob      ##
    ##   4   ##    Tom     ##
    ##   5   ##    Mike    ##
    ##   6   ##    Mike    ##
    ##   7   ##    Joe     ##
    ##   8   ##    Joe     ##
    I need that to group by the name and sort it by how many duplicats there are. Any help?
    Last edited by NoEffinWay; 05-31-2012 at 08:07 AM.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,384
    Something like this?
    Code:
    SELECT name, COUNT(*) AS name_count
    FROM the_table
    GROUP BY name
    ORDER BY name_count DESC
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Feb 2012
    Location
    Tallahassee, FL
    Posts
    280
    Yeah, that would sort the way I wanted. However, i forgot to mention I also needed the count of how many each one repeated. I ended up doing something like:
    PHP Code:
    <?php
    $query 
    mysql_query("SELECT * FROM table");
    while(
    $row mysql_fetch_array($query)){
    $allnames[$row['name']][] = $row['name'];
    }
    arsort($allnames);
    foreach(
    $allnames as $an){
    $num count($an);
    echo 
    $an[0]." shows up ".$num." times in the table.";
    }
    Thanks!

  4. #4
    Join Date
    Mar 2006
    Location
    Discworld
    Posts
    75
    That is exactly name_count show
    Last edited by alexsts; 06-05-2012 at 10:47 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles