title: How can I express joining to a grouped subquery using NHibernate?
body: <p>I'm trying to express a SQL query using NHibernate's Criteria API, and I'm running into difficulty because I'm thinking in a database-centric way while NHibernate is object-centric.</p>
  
  <p>SQL (works great):</p>
  
  <pre><code>select outerT.id, outerT.col1, outerT.col2, outerT.col3
  from tbl outerT
  inner join
      (select max(innerT.id)
       from tbl innerT
       group by innerT.col1) grpT
  on outerT.id = grpT.id
  </code></pre>
  
  <p>Essentially, this is a self-join of a table against a subset of itself.  I suppose I could try turning the self-join into a restriction:</p>
  
  <pre><code>select outerT.id, outerT.col1, outerT.col2, outerT.col3
  from tbl outerT
  where outerT.id in (select max(innerT.id) from tbl innerT group by innerT.col1)
  </code></pre>
  
  <p>But I'm not sure how to express that using NHibernate either; I'm fighting with the DetachedCriteria's ProjectionList and wanting to select only <code>max(id)</code> while grouping by <code>col1</code>.</p>
  
  <p>Thanks so much for your suggestions!</p>