Today I was needing to create a query which looked a bit like this:

SELECT   max(this_.SEQUENCE) as y0_,
         this_.ADMISSION_ID  as y1_
FROM     PT_Status this_
GROUP BY this_.ADMISSION_ID

As you can see, I was trying to do a group by one column and a Max() on another column (legacy DB’s gotta love them).

Since I only wanted to project a few properties I decided to use project and provide a list of columns I wanted, my code looked like:

var detachedCriteria = CriteriaBuilder.CreateCritieraFor()
	.SetProjection( 
		Projections.ProjectionList()                    
        		.Add( Projections.Property( "Sequence" ) ) 
	                .Add( Projections.Property( "AdmissionID" ) )                    
			)                
	.SetProjection( Projections.Max( "Sequence" ) )
	.SetProjection( Projections.GroupProperty( "AdmissionID" ) )
        .SetResultTransformer( Transformers.AliasToBean( typeof( Status ) ) );

When I would run this code it would not give me the sql I wanted, and everything looked ok.   Then through trial and error I realized my issue.  The code below is the code which works

var detachedCriteria = CriteriaBuilder.CreateCritieraFor()
	.SetProjection( 
		Projections.ProjectionList()                    
        		.Add( Projections.Max( "Sequence" ) ) 
	                .Add( Projections.GroupProperty( "AdmissionID" ) )                    
			)                
        .SetResultTransformer( Transformers.AliasToBean( typeof( Status ) ) );

If you look at the first example i am both setting up my column projections and my group/max projects, but they are on the same column (not needed) and my group/max projections is OUTSIDE my first projection list.  Turns out that I only needed my first projection list as it would setup the group/max as well as my return columns.

Hope this helps.

Till next time,