title: MYSQL - Group by limit
body: <p>Is there a simple way to LIMIT the GROUP BY results to the top 2.  The following query returns all the results.  Using 'LIMIT 2' reduces the overall list to the top 2 entries only.</p>
  
  <pre><code>select distinct(rating_name), 
         id_markets, 
         sum(rating_good) 'good', 
         sum(rating_neutral)'neutral', 
         sum(rating_bad) 'bad' 
   from ratings 
   where rating_year=year(curdate()) and rating_week= week(curdate(),1)
   group by rating_name,id_markets
   order by rating_name, sum(rating_good) 
   desc
  </code></pre>
  
  <p>Results in the following :-</p>
  
  <blockquote><pre>
  poland  78 48 24 12   &lt;- keep
  poland   1 15  5  0   &lt;- keep
  poland  23 12  6  3
  poland   2  5  0  0
  poland   3  0  5  0
  poland   4  0  0  5
  ireland  1  9  3  0   &lt;- keep
  ireland  2  3  0  0   &lt;- keep
  ireland  3  0  3  0
  ireland  4  0  0  3
  france  12 24 12  6   &lt;- keep
  france   1  3  1  0   &lt;- keep
  france 231  1  0  0
  france   2  1  0  0
  france   4  0  0  1
  france   3  0  1  0
  </pre></blockquote>
  
  <p>Thanks
  Jon</p>
  
  <hr>
  
  <p>As requested I have attached a copy of the table structure and some test data.  My goal is to create a single view that has the top 2 results from each unique rating_name</p>
  
  <pre><code>CREATE TABLE `zzratings` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `id_markets` int(11) DEFAULT NULL,
    `id_account` int(11) DEFAULT NULL,
    `id_users` int(11) DEFAULT NULL,
    `dateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `rating_good` int(11) DEFAULT NULL,
    `rating_neutral` int(11) DEFAULT NULL,
    `rating_bad` int(11) DEFAULT NULL,
    `rating_name` varchar(32) DEFAULT NULL,
    `rating_year` smallint(4) DEFAULT NULL,
    `rating_week` tinyint(4) DEFAULT NULL,
    `cash_balance` decimal(9,6) DEFAULT NULL,
    `cash_spend` decimal(9,6) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `rating_year` (`rating_year`),
    KEY `rating_week` (`rating_week`),
    KEY `rating_name` (`rating_name`)
  ) ENGINE=MyISAM AUTO_INCREMENT=2166690 DEFAULT CHARSET=latin1;
  
  INSERT INTO `zzratings` (`id`,`id_markets`,`id_account`,`id_users`,`dateTime`,`rating_good`,`rating_neutral`,`rating_bad`,`rating_name`,`rating_year`,`rating_week`,`cash_balance`,`cash_spend`)
  VALUES
      (63741, 1, NULL, 100, NULL, 1, NULL, NULL, 'poland', 2010, 15, NULL, NULL),
      (63742, 1, NULL, 101, NULL, 1, NULL, NULL, 'poland', 2010, 15, NULL, NULL),
      (1, 2, NULL, 102, NULL, 1, NULL, NULL, 'poland', 2010, 15, NULL, NULL),
      (63743, 3, NULL, 103, NULL, NULL, 1, NULL, 'poland', 2010, 15, NULL, NULL),
      (63744, 4, NULL, 104, NULL, NULL, NULL, 1, 'poland', 2010, 15, NULL, NULL),
      (63745, 1, NULL, 105, NULL, 1, NULL, NULL, 'poland', 2010, 15, NULL, NULL),
      (63746, 1, NULL, 106, NULL, NULL, 1, NULL, 'poland', 2010, 15, NULL, NULL),
      (63747, 5, NULL, 100, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63748, 5, NULL, 101, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63749, 2, NULL, 102, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63750, 3, NULL, 103, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63751, 4, NULL, 104, NULL, NULL, NULL, 1, 'ireland', 2010, 15, NULL, NULL),
      (63752, 1, NULL, 105, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63753, 1, NULL, 106, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63754, 1, NULL, 100, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63755, 1, NULL, 101, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63756, 2, NULL, 102, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63757, 34, NULL, 103, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63758, 34, NULL, 104, NULL, NULL, NULL, 1, 'ireland', 2010, 15, NULL, NULL),
      (63759, 34, NULL, 105, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63760, 34, NULL, 106, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63761, 21, NULL, 100, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63762, 21, NULL, 101, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63763, 21, NULL, 102, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63764, 21, NULL, 103, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63765, 4, NULL, 104, NULL, NULL, NULL, 1, 'ireland', 2010, 15, NULL, NULL),
      (63766, 1, NULL, 105, NULL, 1, NULL, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63767, 1, NULL, 106, NULL, NULL, 1, NULL, 'ireland', 2010, 15, NULL, NULL),
      (63768, 1, NULL, 100, NULL, 1, NULL, NULL, 'france', 2010, 15, NULL, NULL),
      (63769, 1, NULL, 101, NULL, 1, NULL, NULL, 'france', 2010, 15, NULL, NULL),
      (63770, 2, NULL, 102, NULL, 1, NULL, NULL, 'france', 2010, 15, NULL, NULL),
      (63771, 3, NULL, 103, NULL, NULL, 1, NULL, 'france', 2010, 15, NULL, NULL),
      (63772, 4, NULL, 104, NULL, NULL, NULL, 1, 'france', 2010, 15, NULL, NULL);
  </code></pre>