I'm running statistical operations using hibernate for queries and grouping. Most of the statistical operations I added in a SqlServerDialect. The queried object contains a keyed collection that can returns multiple values or just one. This isn't a problem except when we're grouping by the values of the collection and there's duplicate values. In that case, there's multiple rows and this object shows up multiple times in the stats for that group.

So for example, myObject.myCollection[key] return x, x, y. If we're grouping on myObject.myCollection[key], then for group y myObject.myCollection[differentKey] would have a correct count of 1, but in the grouping of y it would have a count of 2.

Basically it's adding myObject twice to the grouped results because the x grouping appears twice and takes up two rows .

The best answers that seem related suggest using subselect in the from clause. The subselect would use a distinct to force the join to return distinct values for the grouping. This isn't possible in hibernate because you can't have subselects in from clauses. Is there some trick to for the return values of a keyed collections to be unique? Or does anyone have any other thoughts?

Thanks in advance.