title: DQL Select every rows having one column's MAX value
body: <p>Working with Symfony 2 and Doctrine, I'm searching for a way to select every rows having the max value in a specific column.</p>
  
  <p>Right now, I'm doing it in two queries: </p>
  
  <ul>
  <li>One to get the max value of the column in the table</li>
  <li>Then I select rows having this value.</li>
  </ul>
  
  <p>I'm sure this can be done with one query.</p>
  
  <p>Searching, I have found <a href="https://stackoverflow.com/a/7745635/2707018">this answer</a> in <a href="https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column">a thread</a>, that seems to be what I am searching for, but in SQL.</p>
  
  <p>So according to the answer's first solution, the query I'm trying to build would be something like that:</p>
  
  <pre><code>select yt.id, yt.rev, yt.contents
  from YourTable yt
  inner join(
      select id, max(rev) rev
      from YourTable
      group by id
  ) ss on yt.id = ss.id and yt.rev = ss.rev
  </code></pre>
  
  <p>Does anybody know how to make it in Doctrine DQL?</p>
  
  <p>For now, here is the code for my tests (not working):</p>
  
  <pre><code>$qb2= $this-&gt;createQueryBuilder('ms')
              -&gt;select('ms, MAX(m.periodeComptable) maxPeriode')
              -&gt;where('ms.affaire = :affaire')
              -&gt;setParameter('affaire', $affaire);
  
  $qb = $this-&gt;createQueryBuilder('m')
              -&gt;select('m')
              //-&gt;where('m.periodeComptable = maxPeriode')
  
              // This is what I thought was the most logical way of doing it:
              -&gt;innerJoin('GAAffairesBundle:MontantMarche mm, MAX(mm.periodeComptable) maxPeriode', 'mm', 'WITH', 'm.periodeComptable = mm.maxPeriode')
  
              // This is a version trying with another query ($qb2) as subquery, which would be the better way of doing it for me,
              // as I am already using this subquery elsewhere
              //-&gt;innerJoin($qb2-&gt;getDQL(), 'sub', 'WITH', 'm.periodeComptable = sub.maxPeriode')
  
              // Another weird try mixing DQL and SQL logic :/
              //-&gt;innerJoin('SELECT MontantMarche mm, MAX(mm.periodeComptable) maxPeriode ON m.periodeComptable = mm.maxPeriode', 'sub')
  
              //-&gt;groupBy('m')
              -&gt;andWhere('m.affaire = :affaire')
              -&gt;setParameter('affaire', $affaire);
  
      return $qb-&gt;getQuery()-&gt;getResult();
  </code></pre>
  
  <p>The Entity is GAAffairesBundle:MontantMarche, so this code is in a method of the corresponding repository.</p>
  
  <p>More generally, I'm learning about how to handle sub-queries (SQL &amp; DQL) and DQL syntax for advanced queries.</p>
  
  <p>Thx!</p>