title: Doctrine Query Language get Max/Latest Row Per Group
body: <p>I am trying and failing to translate my relatively simple SQL statement into one that will work within Doctrine.</p>
  
  <p>This is the SQL statement, which works as required when run against my database:</p>
  
  <pre><code>SELECT a.*
   FROM score a
   INNER JOIN (
    SELECT name, MAX(score) AS highest
    FROM score
    GROUP BY name
   ) b
   ON a.score = b.highest AND a.name = b.name
   GROUP BY name
   ORDER BY b.highest DESC, a.dateCreated DESC
  </code></pre>
  
  <p>Here's the DQL attempt thus far:</p>
  
  <pre><code>$kb = $em-&gt;createQuery(
      "SELECT a 
      FROM ShmupBundle:Score a
      INNER JOIN a.name ShmupBundle:Score b WITH a.score = b.score AND a.name = b.name GROUP BY b.name
      WHERE a.platform='keyboard'
      GROUP BY a.name
      ORDER BY b.score DESC, a.dateCreated DESC"
  );
  </code></pre>
  
  <p>Which is currently giving this error:</p>
  
  <pre><code>[Semantical Error] line 0, col 73 near 'ShmupBundle:Score': Error: Class ShmupBundle\Entity\Score has no association named name
  </code></pre>
  
  <p>The table itself is pretty simple:
  id, name, score, platform, dateCreated</p>
  
  <p>There are multiple entries with the same name, but different scores. I want to show only the "high score" per name. I've been trying on and off for a day or two now, with no luck. Can anyone point me in the right direction?</p>