Select minimum of a subquery in NHibernate

I am trying to translate this SQL query into a NHibernate solution:

SELECT MIN(TopTimes.StartTime)
FROM (SELECT TOP 100 StartTime FROM Pack ORDER BY StartTime DESC) AS TopTimes

Effectively for the last X (开发者_JAVA百科in this case 100) started packs I want to know what the minimum StartTime is. This doesn't seem complicated but after 2h googling I can't wrap my head around it somehow.

I have the subquery like this so far:

DetachedCriteria.For<Pack>()
    .SetProjection(Projections.Property("StartTime"))
    .SetMaxResults(100)
    .AddOrder(Order.Desc("StartTime"));

But I am not sure how to marry this up with the Projections.Min

Update: To elaborate a bit more:

Example: Let select StartTime from Pack return the following result:

2011-08-05 09:05:04.000
2011-08-05 08:05:04.000
2011-08-05 06:05:04.000
2011-08-05 05:05:04.000
2011-08-05 07:05:04.000

I want to keep the 2 most recent start times:

SELECT TOP 2 StartTime FROM Pack ORDER BY StartTime DESC

which returns:

2011-08-05 09:05:04.000
2011-08-05 08:05:04.000

Then I take the minimum of that which is 2011-08-05 08:05:04.000 and that's what I am after.

The suggested select top 2 StartTime from Pack order by StartTime ASC will return:

2011-08-05 05:05:04.000
2011-08-05 06:05:04.000

which is not what I need.

Based on your comments on the question. You don't need to use 'MIN' at all.

'MIN' will return you a single result. If you had some criteria, with grouping, then you could return the minimum value of each group. But based on your information it's not required.

At the moment you're using DESC (Descending) which will list the dates from newest to oldest, so you would end up with say:

2011-06-15
2011-05-18
2011-05-13
2011-04-07

You want to update your query to use ASC (Ascending), so that you get the oldest date first, which will give you the same results like:

2011-04-07
2011-05-13
2011-05-18
2011-06-15

To update your query all you need to do is:

DetachedCriteria.For<Pack>()
    .SetProjection(Projections.Property("StartTime"))
    .SetMaxResults(100)
    .AddOrder(Order.Asc("StartTime"));

If you need this as a sub-query then can you please provide more information.

So you want to order all the results, and take the minimum date from that?

Then you could do:

DetachedCriteria.For<Pack>()
    .SetProjection(Projections.Min(Projections.Property("StartTime")))
    .SetMaxResults(100)
    .AddOrder(Order.Desc("StartTime"));

This would give you the lowest/oldest single date result from the last 100 results results.