Joseph Joseph - 14 days ago 5
SQL Question

How can I SELECT rows with MAX(Column value) in DQL?

I can not get this query in a symfony2 project that I have.

My Table:

id course datetime numOrden
---|-----|------------|--------
1 | 1º | 04/11/2016 | 1
2 | 2º | 04/11/2016 | 2
5 | 3º | 04/11/2016 | 5
3 | 4º | 03/11/2016 | 4
4 | 5º | 03/11/2016 | 3


I need to get the course whose value in the "numOrden" column is the maximum( in this case it would be the 3rd course). For this I have used the following query in Doctrine2:

public function findCourse()
{
return $this->getEntityManager()->createQuery(
'SELECT c FROM BackendBundle:Curso c WHERE c.numOrden in (SELECT max(c.numOrden) FROM BackendBundle:Curso )')
->getResult();
}


Or

public function findCourse()
{
return $this->getEntityManager()->createQuery(
'SELECT c FROM Bundle:Course c WHERE c.numOrden=(SELECT max(c.numOrden) FROM Bundle:Course )')
->getResult();
}


But it shows the following error:

[Syntax Error] line 0, col -1: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got end of string. (500 Internal Server Error)

Answer

Try using another alias in the subselect as:

    public function findCourse()
{
    return $this->getEntityManager()->createQuery(
    'SELECT c FROM Bundle:Course c WHERE c.numOrden=(SELECT max(co.numOrden) FROM Bundle:Course co )')
    ->getResult();
}

Hope this help

Comments