title: Get most common value for each value of another column in SQL
body: <p>I have a table like this:</p>
  
  <pre><code> Column  | Type | Modifiers 
  ---------+------+-----------
   country | text | 
   food_id | int  | 
   eaten   | date | 
  </code></pre>
  
  <p>And for each country, I want to get the food that is eaten most often.  The best I can think of (I'm using postgres) is:</p>
  
  <pre><code>CREATE TEMP TABLE counts AS 
     SELECT country, food_id, count(*) as count FROM munch GROUP BY country, food_id;
  
  CREATE TEMP TABLE max_counts AS 
     SELECT country, max(count) as max_count FROM counts GROUP BY country;
  
  SELECT country, max(food_id) FROM counts 
     WHERE (country, count) IN (SELECT * from max_counts) GROUP BY country;
  </code></pre>
  
  <p>In that last statement, the GROUP BY and max() are needed to break ties, where two different foods have the same count.</p>
  
  <p>This seems like a lot of work for something conceptually simple.  Is there a more straight forward way to do it?</p>