Hello.

Have the following issue:

Lets say I have 2 persistent classes:

public class Item {
public Long id;
public String name;
public List remarks;
}

public class Remark {
public Long id;
public String text;
public Date creationDate;
public Item item;
}

To query for item names and last item remarks I am using following hql query:

select
item.name,
(select r.remarkText from Remark r where r.creationDate = (select max(rlast.creationDate) from Remark rlast inner join r.item i where i = item)
from Item item

The problem is that sometimes 2 remarks with same creationDate exists. In this case I need any remark for example with largest id

Well if it would be something like "select top" in hql it will look pretty easy:

select
item.name,
(select top 1 r.remarkText from Remark r inner join r.item i where i = item order by r.creationDate desc, r.id desc)
from Item item

But it doesn't parsed in HQL :(((

To make it works the following query is ok:

select
item.name,
(select r.remarkText from Remark r where r.id =
(select max(r1.id) from Remark r1 inner join r1.item i1 where i1 = item and r1.creationDate =
(select max(r2.creationDate) from Remark r2 inner join r2.item i2 where i2 = hobj)
)
)
from Item item

But I hate using such a complex construction as in real application expression will be twice more complex at least than in example I used. Does anybody knows if it is possible to write more simple query in hql to get correct values?

Many thanks in advance

To workaround this situation I created sql server dialect modification:



Well, I don't feel this is how it should be, but it faster than before, more readable and can be expressed in hql rather than create sql just to be able to use subselect properly. Like this:



I don't understand why HQL doesn't support it directly by the syntax. Does anybody have the comments on how it is implemented and/or ideas what can be the better solution?[/code]