title: Alternative to using GROUP BY without aggregates to retrieve distinct "best" result
body: <p>I'm trying to retrieve the "Best" possible entry from an SQL table.</p>
  
  <p>Consider a table containing tv shows:
  id, title, episode, is_hidef, is_verified
  eg:</p>
  
  <pre><code>id title         ep hidef verified
  1  The Simpsons  1  True  False
  2  The Simpsons  1  True  True
  3  The Simpsons  1  True  True
  4  The Simpsons  2  False False
  5  The Simpsons  2  True  False
  </code></pre>
  
  <p>There may be duplicate rows for a single title and episode which may or may not have different values for the boolean fields. There may be more columns containing additional info, but thats unimportant.</p>
  
  <p>I want a result set that gives me the best row (so is_hidef and is_verified are both "true" where possible) for each episode. For rows considered "equal" I want the most recent row (natural ordering, or order by an abitrary datetime column).</p>
  
  <pre><code>3  The Simpsons  1  True  True
  5  The Simpsons  2  True  False
  </code></pre>
  
  <p>In the past I would have used the following query:</p>
  
  <pre><code>SELECT * FROM shows WHERE title='The Simpsons' GROUP BY episode ORDER BY is_hidef, is_verified
  </code></pre>
  
  <p>This works under MySQL and SQLite, but goes against the SQL spec (GROUP BY requiring aggragates etc etc). I'm not really interested in hearing again why MySQL is so bad for allowing this; but I'm very interested in finding an alternative solution that will work on other engines too (bonus points if you can give me the django ORM code for it).</p>
  
  <p>Thanks =)</p>