I've been trying to make a more complex query than I'm used to, but try as I might I can't seem to get the syntax correct.
Is it possible to query the database for a persisted object which has both a Unique foreign key, and a maximum primary key?

That is, if I have multiple objects with the same foreign key and I want all of the ones which have the highest primary key and unique foreign key

1: fk: 4 pk: 1
2: fk: 4 pk: 2
3: fk: 7 pk: 3
4: fk: 7 pk: 4
5: fk: 7 pk: 5
6: fk: 9 pk: 6
7: fk: 9 pk: 7

I want entry 2 because it is the highest id with fId 4
I want etnry 5 because it is the highest id with fId 7
I want entry 7 because it is the highest id with fId 9

Really? This isn't possible?
Could I do something to mimic this function? I have a method right now which is able to do what I want, but I feel it is really inefficient.

It uses two while loops, one to iterate through all of the primary keys of the parent object, and another nested while loop to iterate through the objects with foreign keys matching the current iteration's primary key and only retrieving the last of these objects.

There must be a better way!

You can use a subquery to find the max:

from Entry entry where pk=(select max(pk) from Entry entry1 where entry.fk = entry1.fk)

There are is a way to do this, which should work in hibernate:

create a subquery, selecting the maximum PK grouped by the foreign key.

Your main query selects all rows with the PK included in the subquery.

This aproach should work in any sql dialect supporting subselects and it should be possible to formulate this as

Hibernate Criteria; HQL and definetly as SQL.


If you happen to work against a Oracle Database you can use analytic functions to do this much faster in many cases.

I doubt Hibernate supports analytics through Criteria or HQL, though I might me wrong. But you always use the SQL syntax.

If you're interested in more information about the oracle specific solution let me know.

Jens

I like Jens' suggestion better - probably more efficient than mine.

from Entry entry where pk in (select max(pk) from Entry group by fk)

Thank you, in the end
exactly as you said.