Search code examples
javamysqlhibernatejpajpql

Named Query to SELECT rows with MAX(column name), DISTINCT by another column

I have a case similar to the one described in this question, I wrote an identical query which works, but when I try to write it as a jpql named query, I'm getting an error.

My query:

@NamedQuery(
            name = "findRankingsBetween",
            query = "SELECT rt FROM Rankingtable rt " +
                        "INNER JOIN " +
                            "(SELECT teamId, MAX(lastupdate) as MaxDateTime " +
                            "FROM Rankingtable " +
                            "GROUP BY teamId) grouped " +
                        "ON rt.teamId = grouped.teamId " +
                        "AND rt.lastupdate = grouped.MaxDateTime " +
                    "WHERE rt.lastupdate BETWEEN :from AND :to"
            )

Error:

Error in named query: findRankingsBetween: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 79

How to write the query properly in jpql?

Solution

  • As noted in this answer, a subquery in JPQL can only occur in select and where clauses. Hibernate doc.

    An equivalent query in JPQL is:

    "SELECT rt FROM Rankingtable rt " +
"WHERE rt.lastupdate = (SELECT MAX(r2.lastupdate) " +
                       "FROM Rankingtable r2 " +
                       "WHERE r2.teamid = rt.teamid) " +
"AND rt.lastupdate BETWEEN :from AND :to"