JPQL/SQL get the latest of records grouped by columns

Question

I'm currently using JPQL (latest version I think) with a postgresql database and I'm trying to get the latests objects grouped by some (not all columns).

Let's say I have a table MyTable like this:

id | name | color | shape | date_of_creation | other_stuff|
---+------+-------+-------+------------------+------------+
01 | apple|    red| circle|        30/12/2015|       stuff|
02 | apple|    red| circle|        15/12/2015|   somestuff|
03 | apple|  green| circle|        01/12/2015|anotherstuff|
04 |   box| orange|  cubic|        13/12/2015|   blahblah1|
05 |   box| orange|  cubic|        25/12/2015|   blahblah2|

And I only want the latest grouped by name, color and shape like this:

id | name | color | shape | date_of_creation | other_stuff|
---+------+-------+-------+------------------+------------+
01 | apple|    red| circle|        30/12/2015|       stuff|
05 |   box| orange|  cubic|        25/12/2015|   blahblah2|

Let's assume that the java equivalent is MyClass with the same elements.

My query in JPQL would look like something like:

SELECT m FROM MyClass m GROUP BY m.name, m.color, m.shape ORDER BY m.date_of_creation;

But this does not work because "date_of_creation" must be in the "GROUP BY" clause, which does not make sense because I don't want to group by date_of_creation. I tried using nested select but I have the same problem with the id now..

I found out about the greatest-n-per-group problem ( SQL Select only rows with Max Value on a Column ) on SO's title suggestions after orginaly typing the question, so I created a named query like this:

SELECT m FROM Myclass LEFT JOIN m.id mleft WHERE 
    m.name = mleft.name AND 
    m.color = mleft.color AND
    m.shape = mleft.shape AND
    m.date_of_creation < mleft.date_of_creation AND
    mleft.id = null

Which makes a java.lang.NullPointerException at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:395) or does not even work (no error, nothing). I think it's because the field "id" does not have a relationship with itself. And using an inner join with a select inside does not seem to work in JPQL:

SELECT m FROM MyClass INNER JOIN (SELECT mInner from Myclass GROUP BY m.name etc.)

Maybe I'm missing something and I'll have to build a much more complicated query, but JPQL seems not to be powerfull enough, or I don't know enough of it. Also, I don't really feel like using the query.getResultList() and doing the work in java...

Any tips are welcome, thanks in advance.

Answer 1

You can't use subqueries in FROM block in JPQL, only SELECT and WHERE blocks allowed. Try someting like this:

SELECT m 
FROM Myclass m
WHERE m.date_of_creation IN ( SELECT MAX(mm.date_of_creation)
    FROM Myclass mm
    WHERE mm.id = m.id)

P.S. Code not tested