title: How to do Max in count(*) with Hive?
body: <p>I have two tables :</p>
  
  <p><strong>Fly</strong> : <em>Year</em>, <em>Origin</em></p>
  
  <p><strong>Airport</strong> : <em>Code</em>, <em>Name</em></p>
  
  <p>Here is a sample of data :</p>
  
  <p><strong>Fly</strong> :</p>
  
  <pre><code>1989,SF    
  1989,SF   
  1989,NY  
  1993,NY  
  1998,Par     
  1998,Par  
  1998,NY
  </code></pre>
  
  <p><strong>AirPort</strong> :    </p>
  
  <pre><code>SF, International Airport    
  NY, Inter Air    
  Par, Charles de Gaulle
  </code></pre>
  
  <p>I want to get the most used airport per year.</p>
  
  <p>So firstly I did this request to get ths number of occurence of each airport per each year :</p>
  
  <pre><code>SELECT v.Year,a.airport ,count(*)
  From airports a JOIN Vol v ON (a.iata = v.Dest)
  Group By v.Year,a.airport
  ORDER BY Year ASC,airport ASC;
  </code></pre>
  
  <p>So i get this kind of result :</p>
  
  <pre><code>1989, San Francisco, 2  
  1989, New York, 1
  1993, New York, 1
  1998, new York, 1
  1998, Paris, 2
  </code></pre>
  
  <p>And I want the max of each year like this :  </p>
  
  <pre><code>1989, San Francisco, 2
  1993, New York, 1
  1998, Paris, 2
  </code></pre>
  
  <p>Can i do it with one single request ? 
  Should i use an intermediate table ?</p>
  
  <p>Is it better with Pig ?</p>
  
  <p>Thank you in advance</p>