title: How to select all rows with max values in a derived table?
body: <p>I have the following table called &quot;table3&quot;</p>
  <pre><code>+------+
  | name |
  +------+
  | aaa  |
  | aaa  |
  | aaa  |
  | aaa  |
  | aaa  |
  | aaa  |
  | bbb  |
  | bbb  |
  | bbb  |
  | bbb  |
  | ccc  |
  | ccc  |
  | ccc  |
  | ccc  |
  | ccc  |
  | ccc  |
  | ccc  |
  | ccc  |
  | ccc  |
  +------+
  </code></pre>
  <p>when I got count for each name, I got</p>
  <pre><code>mysql&gt;select name,count(name) as count from table3 group by name order by name;
  +------+-------+
  | name | count |
  +------+-------+
  | aaa  |     6 |
  | bbb  |     4 |
  | ccc  |     9 |
  +------+-------+
  </code></pre>
  <p>so I ran the following query to return and names and max count net to them</p>
  <pre><code>mysql&gt; select name from (select name,count(name) as count from table3 group by name order by name) as tbl where tbl.count=(select max(count) from tbl);
  </code></pre>
  <p>expecting the following result</p>
  <pre><code>+------+
  | name |
  +------+
  | ccc  |
  +------+
  </code></pre>
  <p>but I get this error</p>
  <pre><code>ERROR 1146 (42S02): Table 'tests.tbl' doesn't exist
  </code></pre>
  <p>Ihave done aliasing correctly, but the table alias &quot;tbl&quot; isn't identified in the part &quot;.....unt=(select max(count) from tbl)&quot;</p>
  <p>then I used this queries too</p>
  <pre><code>mysql&gt; select name from (select name,count(name) as count from table3 group by name order by name) as tbl where tbl.count=(select max(count) from tbl) as tb1;
  
  mysql&gt; select name from (select name,count(name) as count from table3 group by name order by name) as tbl where tbl.count=((select max(count) from tbl) as tb1);   //extra couple of parenthesis 
  </code></pre>
  <p>they also give some different errors. Nothing was succeeded</p>
  <p>How to fix this problem <strong>without deriving another table from the original table (table3)</strong></p>