title: Select one value from a group based on order from other columns
body: <h1>Problem</h1>
  
  <p>Suppose I have this table <code>tab</code> (<a href="http://www.sqlfiddle.com/#!2/ad3987/2" rel="nofollow noreferrer">fiddle</a> available).</p>
  
  <pre class="lang-none prettyprint-override"><code>| g | a | b |     v |
  ---------------------
  | 1 | 3 | 5 |   foo |
  | 1 | 4 | 7 |   bar |
  | 1 | 2 | 9 |   baz |
  | 2 | 1 | 1 |   dog |
  | 2 | 5 | 2 |   cat |
  | 2 | 5 | 3 | horse |
  | 2 | 3 | 8 |   pig |
  </code></pre>
  
  <p>I'm grouping rows by <code>g</code>, and for each group I want one value from column <code>v</code>. However, I don't want <em>any</em> value, but I want the value from the row with maximal <code>a</code>, and from all of those, the one with maximal <code>b</code>. In other words, my result should be</p>
  
  <pre class="lang-none prettyprint-override"><code>| 1 |   bar |
  | 2 | horse |
  </code></pre>
  
  <h1>Current solution</h1>
  
  <p>I know of a query to achieve this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT grps.g,
  (SELECT v FROM tab
   WHERE g = grps.g
   ORDER BY a DESC, b DESC
   LIMIT 1) AS r
  FROM (SELECT DISTINCT g FROM tab) grps
  </code></pre>
  
  <h1>Question</h1>
  
  <p>But I consider this query rather <strong>ugly</strong>. Mostly because it uses a <strong>dependant subquery</strong>, which feels like a real performance killer. So I wonder whether there is an easier solution to this problem.</p>
  
  <h1>Expected answers</h1>
  
  <p>The most likely answer I expect to this question would be some kind of add-on or patch for MySQL (or MariaDB) which does provide a feature for this. But I'll welcome other useful inspirations as well. Anything which works without a dependent subquery would qualify as an answer.</p>
  
  <p>If your solution only works for a single ordering column, i.e. couldn't distinguish between <code>cat</code> and <code>horse</code>, feel free to suggest that answer as well as I expect it to be still useful to the majority of use cases. For example, <code>100*a+b</code> would be a likely way to order the above data by both columns while still using only a single expression.</p>
  
  <p>I have a few pretty hackish solutions in mind, and might add them after a while, but I'll first look and see whether some nice new ones pour in first.</p>
  
  <hr>
  
  <h1>Benchmark results</h1>
  
  <p>As it is pretty hard to compare the various answers just by looking at them, I've run some benchmarks on them. This was run on my own desktop, using MySQL 5.1. The numbers won't compare to any other system, only to one another. You probably should be doing your own tests with your real-life data if performance is crucial to your application. When new answers come in, I might add them to my script, and re-run all the tests.</p>
  
  <ul>
  <li>100,000 items, 1,000 groups to choose from, InnoDb:
  
  <ol>
  <li>0.166s for <a href="https://stackoverflow.com/q/12726549">MvG (from question)</a></li>
  <li>0.520s for <a href="https://stackoverflow.com/a/12728212">RichardTheKiwi</a></li>
  <li>2.199s for <a href="https://stackoverflow.com/a/12726666">xdazz</a></li>
  <li>19.24s for <a href="https://stackoverflow.com/a/12727443">Dems</a> (sequential sub-queries)</li>
  <li>48.72s for <a href="https://stackoverflow.com/a/12726837">acatt</a></li>
  </ol></li>
  <li>100,000 items, 50,000 groups to choose from, InnoDb:
  
  <ol>
  <li>0.356s for <a href="https://stackoverflow.com/a/12726666">xdazz</a></li>
  <li>0.640s for <a href="https://stackoverflow.com/a/12728212">RichardTheKiwi</a></li>
  <li>0.764s for <a href="https://stackoverflow.com/q/12726549">MvG (from question)</a></li>
  <li>51.50s for <a href="https://stackoverflow.com/a/12726837">acatt</a></li>
  <li><em>too long</em> for <a href="https://stackoverflow.com/a/12727443">Dems</a> (sequential sub-queries)</li>
  </ol></li>
  <li>100,000 items, 100 groups to choose from, InnoDb:
  
  <ol>
  <li>0.163s for <a href="https://stackoverflow.com/q/12726549">MvG (from question)</a></li>
  <li>0.523s for <a href="https://stackoverflow.com/a/12728212">RichardTheKiwi</a></li>
  <li>2.072s for <a href="https://stackoverflow.com/a/12727443">Dems</a> (sequential sub-queries)</li>
  <li>17.78s for <a href="https://stackoverflow.com/a/12726666">xdazz</a></li>
  <li>49.85s for <a href="https://stackoverflow.com/a/12726837">acatt</a></li>
  </ol></li>
  </ul>
  
  <p>So it seems that my own solution so far isn't all that bad, even with the dependent subquery. Surprisingly, the solution by acatt, which uses a dependent subquery as well and which I therefore would have considered about the same, performs much worse. Probably something the MySQL optimizer can't cope with. The solution RichardTheKiwi proposed seems to have good overall performance as well. The other two solutions heavily depend on the structure of the data. With many groups small groups, xdazz' approach outperforms all other, whereas the solution by Dems performs best (though still not exceptionally good) for few large groups.</p>