views:

30

answers:

1

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.