title: Limiting GROUP BY based on COUNT() values in mySQL
body: <p>I'm logging events into a mySQL database and would like to get the top 3 events for monitoring purposes.</p>
  
  <p>My table <code>eventlog</code> looks like this:</p>
  
  <pre><code>+----+------------------+---------------------+
  | id |    eventname     |      eventdate      |
  +----+------------------+---------------------+
  |  0 | machine1.started | 2016-09-04 19:22:23 |
  |  1 | machine2.reboot  | 2016-09-04 20:23:11 |
  |  2 | machine1.stopped | 2016-09-04 20:24:12 |
  |  3 | machine1.started | 2016-09-04 20:25:12 |
  |  4 | machine1.stopped | 2016-09-04 23:23:16 |
  |  5 | machine0.started | 2016-09-04 23:24:00 |
  |  6 | machine1.started | 2016-09-04 23:24:16 |
  |  7 | machine3.started | 2016-09-04 23:25:00 |
  |  8 | machine4.started | 2016-09-04 23:26:00 |
  |  9 | cluster.alive    | 2016-09-04 23:30:00 |
  | 10 | cluster.alive    | 2016-09-05 11:30:00 |
  +----+------------------+---------------------+
  </code></pre>
  
  <p>The query should eventually return the following, holding</p>
  
  <ul>
  <li>the top 3 events that occurred most often (based on column <code>eventcount</code>s that is generated by mySQL's <code>COUNT()</code> function), grouped by their <code>eventname</code></li>
  <li>only 2 rows where <code>eventcount</code> = 1, but only if 1 is within the top 3 <code>eventcounts</code> (since there are a lot of events that occur just
  once and therefore would overload my frontend)</li>
  </ul>
  
  <p>Example of the desired result, based on the above table:</p>
  
  <pre><code>+------------+------------------+
  | eventcount |    eventname     |
  +------------+------------------+
  |          3 | machine1.started |
  |          2 | machine1.stopped |
  |          2 | cluster.alive    |
  |          1 | machine0.started |
  |          1 | machine2.started |
  +------------+------------------+
  </code></pre>
  
  <p><em>Please note that I do not need just 3 returned rows but the rows with the 3 highest <code>eventcount</code>s.</em></p>
  
  <p>I did a lot of experimenting by messing around with the query string below, including multiple selects and questionable <code>CASE ... WHEN</code> conditions, but wasn't able to make it work the way I need.</p>
  
  <pre><code>SELECT COUNT(id) AS 'eventcount', eventname
  FROM eventlog
  GROUP BY eventname
  ORDER BY eventcount DESC;
  </code></pre>
  
  <p>What is the best approach to get the desired result in a performant way?</p>