title: Efficiently take min of a column with other column as the key
body: <p>I recently transitioned over from using Presto to Hive. I have the following scenario. Column A, B, C. I want to aggregate on A and find the value of B for which the value of C is minimized. In presto you can something like this as
  <code>
  SELECT A, min_by(B, C) from &lt;TABLE&gt; GROUP BY A
  </code>
  Now I want to do the same thing in Hive. But unfortunately I couldn't find a UDF similar to this anywhere in the documentation. Now I know I can do the following</p>
  
  <pre><code>SELECT A, COALESCE(B, 0)
  from &lt;TABLE&gt; as primary
  JOIN (
  SELECT A, MIN(C) as C FROM &lt;TABLE&gt; GROUP BY A 
  ) secondary
  ON primary.A = secondary.A AND primary.C = secondary.C\
  GROUP BY A
  </code></pre>
  
  <p>I have 2 problems with this solution</p>
  
  <ol>
  <li>It's not concise at all.</li>
  <li>It's not efficient either. I am doing an extra subquery resulting and an extra aggregation and an extra JOIN. It would be nice to have a first class aggregation support for such a function.</li>
  </ol>
  
  <p>Is there a way to achieve what I am trying to do without writing your custom UDF ?</p>