SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict Robert_2006's Avatar
    Join Date
    Jun 2006
    Location
    Tarpon Springs Fl.
    Posts
    265
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Group with most members?

    The members of my site can create groups. I have sored them in the DB with the members id and the groups id. How can I find the group with the most members? In the case below I would like to return group_id 1.

    id |userid |group_id |status |joindate
    ------------------------------------
    1***|1****|1*******|1*****|1180472138
    2***|1****|34******|1*****|1180710824
    3***|59***|1*******|1*****|1180661614
    4***|1****|33******|1*****|1180661614
    5***|1****|25******|1*****|1180627931
    6***|1****|24******|1*****|1180627804

    CREATE TABLE `my_group` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `userid` int(10) unsigned NOT NULL default '0',
    `group_id` smallint(6) NOT NULL default '0',
    `status` tinyint(1) unsigned NOT NULL default '0',
    `joindate` int(10) unsigned NOT NULL default '0',
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;

    --
    -- Dumping data for table `my_group`
    --

    INSERT INTO `my_group` VALUES (1, 1, 1, 1, 1180472138);
    INSERT INTO `my_group` VALUES (25, 1, 34, 1, 1180710824);
    INSERT INTO `my_group` VALUES (26, 59, 1, 1, 1180661614);
    INSERT INTO `my_group` VALUES (24, 1, 33, 1, 1180661614);
    INSERT INTO `my_group` VALUES (16, 1, 25, 1, 1180627931);
    INSERT INTO `my_group` VALUES (15, 1, 24, 1, 1180627804);

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

  3. #3
    SitePoint Addict Robert_2006's Avatar
    Join Date
    Jun 2006
    Location
    Tarpon Springs Fl.
    Posts
    265
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I appreciate it. Thanks!


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
  •