1

I'm using Hibernate Criteria, I want to select the maximun value for each group of values, this maximun value must be contained in a given list.

Example:

select t.field1, max(t.field2) as total
from table t
where t.field2 in :givenList 
group by t.field1
order by total desc;

I've tried doing this:

Criteria criteria = session.createCriteria(Table.class);
DetachedCriteria maxNo = DetachedCriteria.forClass(Table.class);
ProjectionList projection = Projections.projectionList();
projection.add(Projections.groupProperty("id.field1"));
projection.add(Projections.max("id.field2"));
maxNo.setProjection(projection);
criteria.add(Subqueries.propertiesIn(new String[] {"id.field1", "id.field2"}, maxNo));

This code works perfectly but it returns only the max value for each group.

If I try to add other constrains like:

criteria.add(Property.forName("id.field2").in(givenList));

The request doesn't work properly. Basic case, the maximun for the group is 2, and the given list is (0,1), in this case we receive nothing for this group.

I hope you could help me. Thanks in advance!

Neil Stockton
  • 11,383
  • 3
  • 34
  • 29
gcotis
  • 107
  • 2
  • 15

1 Answers1

0

I've found the solution. 

maxNo.add(Restrictions.in("id.field2", givenList));

Actually, I make a mistake placing the Restrictionsin the Criteria. We should place the Restrictions in the DetachedCriteria.

Thank you anyway

gcotis
  • 107
  • 2
  • 15