Max date in an nHibernate HQL query
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
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"
)