NHibernate Aggregate Subquery?

For the HQL world a single query does the trick.

For the HQL world a single query does the trick... var maxScore = session. CreateQuery(@"select sum(game. Score) from Game game group by game.

Match order by sum(game. Score) desc") . SetMaxResults(1) .UniqueResult(); Hope this helps.. Update: And for the Criteria world, may be there is a better result transformation but this just works :) var max = (int)session.

CreateCriteria("game") . SetProjection(Projections.ProjectionList() . Add(Projections.

GroupProperty("game. Match")) . Add(Projections.

Sum("game. Score"), "total")) . AddOrder(Order.

Desc("total")) . SetMaxResults(1) . SetResultTransformer(Transformers.

AliasToEntityMap) .UniqueResult()"total".

Thanks, the HQL works a treat. – Carl Nov 5 '09 at 19:40.

I would actually do it this way in SQL: select top 1 Match. ID, sum(Game. Score) as total from Game inner join Match on Game.

Match_ID = Match. ID group by Match. ID order by total desc group by is always tricky in Criteria/HQL: because a group by clause can only return the grouped column and aggregates of any other column.

Therefore it is impossible to return a whole entity from a group by clause, only the ID that you are grouping by and aggregates. For this reason I usually do group by queries using native SQL like this: ISQLQuery sqlQuery1 = NInstance.GetSession(). CreateSQLQuery("select Match.ID, sum(Game.

Score) as total from Game inner join Match on Game. Match_ID = Match.ID group by match. ID order by total desc"); sqlQuery1.

AddScalar("id", NInt32); // sqlQuery1. AddScalar("total", NInt32); sqlQuery1. SetMaxResults(1); var result = sqlQuery1.List().

I didn't even think about changing the query like that, thanks! I'm actually 'one of those' that would have previously done most things as stored procedures, so putting SQL in my code is probably not gonna happen. HQL scared me at first, but I think that it is pretty powerful, so I'll be going down that route I think.

– Carl Nov 5 '09 at 19:39 Yeah! Except the other guy pinched my SQL and made it into HQL! – reach4thelasers Nov 6 '09 at 18:03.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.