title: mysql world database: select country with max population
body: <p>I want to select the country with the highest population. This is my query:</p>
  
  <pre><code>SELECT continent, name, population FROM country HAVING population = MAX(population);
  </code></pre>
  
  <p>Somehow it returns 0 rows.</p>
  
  <p>If i use a nested query it works:</p>
  
  <pre><code>SELECT continent, name, population FROM country WHERE population = (SELECT MAX(population) FROM country);
  </code></pre>
  
  <p>So my question is: What is wrong with the first query?</p>
  
  <p>PS: You can download the database here:
  <a href="http://dev.mysql.com/doc/index-other.html" rel="nofollow">http://dev.mysql.com/doc/index-other.html</a></p>
  
  <hr>
  
  <p>Alright i think finally i figured out the whole process. Here how it works and why the alternative suggestions/solutions didn't work:</p>
  
  <p>(First of all, as "Dan Bracuk" said in his answer, we have to combine GROUP BY with HAVING and add the aggregate function at the begininng in SELECT statement)</p>
  
  <p>So let's go step by step and try this:</p>
  
  <pre><code>SELECT continent, name, population, MAX(population) FROM country;
  </code></pre>
  
  <p>This yields us the first row appended MAX(pop) at the end: <br/>
  <strong>"North America", "Aruba", "103000", "1277558000"</strong></p>
  
  <p>So MAX(population) is just one entry so it restricts the row number to 1 and because i added the continent, name, population columns, mysql just selects the first row from the table.</p>
  
  <p>So if i now write down:</p>
  
  <pre><code>SELECT continent, name, population, MAX(population) FROM country HAVING population = MAX(population);
  </code></pre>
  
  <p>I get 0 rows, because 103000 is not equal to 1277558000.</p>
  
  <p>If i use:</p>
  
  <pre><code>SELECT continent, name, population, MAX(population) FROM country GROUP BY name;
  </code></pre>
  
  <p>for instance, i get a list of all countries where on each row MAX(population) = population.</p>
  
  <p>So additionally adding "HAVING population = MAX(population)" has no effect since it's already true.</p>
  
  <p>I hope i understood it correctly and could clarify others who wondered why the other solutions didnt work.</p>