Exclude specific value from a Min/Max agregate funcion using ICriteria.
- by sparks
I have a schedule (Voyages) table like this:
ID Arrival Departure OrderIndex
1 01/01/1753 02/10/2009 0
1 02/11/2009 02/15/2009 1
1 02/16/2009 02/19/2009 2
1 02/21/2009 01/01/1753 3
2 01/01/1753 03/01/2009 0
2 03/04/2009 03/07/2009 1
2 03/09/2009 01/01/1753 2
By design i save '01/01/1753' as a default value if the user doesn't fill a the field on the capture screen and for the very first Arrival and the very last Departure which are never provided.
Im using Nhibernate and Criteria, and im wondering whats the best way to query this data if i want to know the First departure and last arrival for each voyage in the table.
My first thought was a groupby (ID) and then do some Min and Max with the arrival and departure but the `'01/01/1753' VALUE is messing aronud.
...
.SetProjection(Projections.ProjectionList()
.Add(Projections.GroupProperty("ID"), "ID")
.Add(Projections.Min("DepartureDate"), "DepartureDate")
.Add(Projections.Max("ArrivalDate"), "ArrivalDate")
)
...
So is there a way to skip this value in the Min function comparison (without losing the whole row of data), or there is a better way to do this, maybe utilizing the OrderIndex that always indicate the correct order of the elements, maybe ordering ASC taking the 1st and then Order DESC and taking the 1 st again, but im not quite sure how to do that with criteria syntax.
