Usage of aggregate function with HQL

du-it · **Joined:** Thu Aug 10, 2006 7:43 am **Posts:** 14

I am working with Hibernate 3.1.3 and I wonder how I can tell Hibernate to give me a result for the following question:

A table has some columns (of course) and one of them is called modified and another is called dataId.
Each time a data is modified this will be protocolized in this table. Thus, it is possible that we have n entries for a dataId x.

I want to get the complete row from the DB/Hibernate specifying the dataId and using max(modified).

How is the syntax? I don't get it work.

I tried something like the following:

Code:

List result = getHibernateTemplate().find("from MyData where data = " + dataId + " having max(modified)");

Thank you,
Dirk

passos · **Posted:** Wed Jan 24, 2007 10:36 am

SQL Having is apply with group by

Use a subquery to searsh max modified

Code:

from MyData md where md.data = " + dataId + " and md.modified = 
( select max( mdt.modified ) from MyData mdt where md.data = mdt.data )

du-it · **Joined:** Thu Aug 10, 2006 7:43 am **Posts:** 14

passos wrote:

SQL Having is apply with group by

Use a subquery to searsh max modified

Code:

from MyData md where md.data = " + dataId + " and md.modified = 
( select max( mdt.modified ) from MyData mdt where md.data = mdt.data )

Great! It works very well.
Thanky you.