Hi,

I have an entity Calc which has columns serialNo, articleNo, seasonNo and country as category identifier (i.e. all rows with the same value of this columns are members of the same category) and some additional columns. For each category there is a version number column (version=1..n), which differs in categories. To get a resultset that contains the maximum value of each category I am using:

select c.serialNo, c.articleNo, c.seasonNo, c.country, max(version) from Calc c group by c.serialNo, c.articleNo, c.seasonNo, c.country having seasonNo='xxxx'
this is working in native sql and hibernate as well.

Now I want to fetch all columns of my table, which is working in sql like this:
select * from Calc calc inner join (select c.serialNo, c.articleNo, c.seasonNo, c.country, max(version) from Calc c group by c.serialNo, c.articleNo, c.seasonNo, c.country) res
on calc.serialNo=res.serialNo and calc.articleNo=res.articleNo and calc.seasonNo=res.seasonNo and calc.country=res.country

How would I setup the equivalent statement in hibernate. Tried it using hql and criteria without success. Any help appreciated.

regards Uwe