title: NHibernate QueryOver subquery to return only newest record
body: <p>I have a query in Nhibernate QueryOver which brings back a collection of episode objects (episode being a spell of care) which in turn has a collection of episode statuses as a property of each episode. However I want to change this so that each episode only brings back the latest status update for that episode instead of all of them.</p>
  
  <p>The SQL to do this is as follows:</p>
  
  <pre><code>SELECT  *
  FROM    DIPEpisode e
  INNER JOIN DIPEpisodeStatus s on s.EpisodeID = e.SequenceID
  WHERE e.ClientID = '1000001'
  AND s.SequenceID IN (
  SELECT TOP 1 SequenceID
  FROM DIPEpisodeStatus s
  WHERE s.EpisodeID = e.SequenceID
  ORDER BY StatusRecordedDate DESC
  )
  </code></pre>
  
  <p>I have written the following query which gives me almost exactly what I need</p>
  
  <pre><code>var statuses =
              QueryOver.Of&lt;DIPEpisodeStatus&gt;()
              .OrderBy(x =&gt; x.StatusRecordedDate).Desc
              .Select(x =&gt; x.Id).Take(1);
  
  DIPEpisodeStatus statusAlias = null;
  
          return
              session.QueryOver&lt;DIPEpisode&gt;()
              .JoinQueryOver(x =&gt; x.DIPEpisodeStatuss, () =&gt; statusAlias)
              .Fetch(x =&gt; x.AgencyID).Eager
              .Fetch(x =&gt; x.DIPEpisodeStatuss).Eager
              .Where(e =&gt; e.ClientID.Id == this.clientId)
              .WithSubquery.WhereProperty(x =&gt; x.Id).Eq(statuses)
              .List();
  </code></pre>
  
  <p>This generates the following SQL:</p>
  
  <pre><code>SELECT *
  FROM   DIPEpisode this_
     inner join DIPEpisodeStatus statusalia1_
       on this_.SequenceID = statusalia1_.EpisodeID
  WHERE  statusalia1_.ClientID = '1000001' /* @p0 */
     and statusalia1_.SequenceID = (SELECT TOP (1 /* @p1 */) this_0_.SequenceID as y0_
                                    FROM   DIPEpisodeStatus this_0_
                                    ORDER  BY this_0_.StatusRecordedDate desc)
  </code></pre>
  
  <p>As you can see, the only thing missing is the where clause from the subquery. What changes do I need to make to the query in order to generate this extra where clause and pull back only the most recent status update?</p>
  
  <p>Thanks</p>
  
  <p>Ben</p>