Max date in an nHibernate HQL query

zaq picture zaq · Nov 30, 2011 · Viewed 14.6k times · Source

I am having some difficultly writing an hql query that will search a table for all the rows where certain criteria are met but I only want to return the row with the max date.

For example the where clause of the query would be something like:

column1 = input1 and column2 = input2 and date_column < date_input

This might return several rows with different dates but I am only interested in the one with the latest date. I also need it to return the entire row.

Presently, I am executing the query with the criteria above ordered descending by date and then fetching the first item in C#. I would like to have an hql query or iCriteria solution that can do this in one step if possible.

Answer

Mark Perry picture Mark Perry · Dec 1, 2011

You just need to use a subquery. Select the max date from the qualifying rows and then select out all records which match the max date. My HQL is a little rusty but you get the idea.

from TableA a 
where a.Date in (
    select max(b.Date) 
    from TableA b 
    where b.Something="You get the idea"
)

Subqueries, Aggregate Function