How to use ROW_NUMBER() in HQL?

My databse is MS SQL. I want to use ROW_NUMBER() in HQL. Is it possible? How? I know about Custom Functions. But prefer a way that does no开发者_StackOverflow社区t need to modify web.config.

UPDATE:

My final goal is to delete all records in a table/entity except than last n records with HQL. I don't like to load all of them in memory then deleting them.

as of NH v2 the HQL supports delete and update statements via IQuery.ExecuteUpdate(). You could experiment using also the IQuery.SetMaxResults() and IQuery.SetFirstResult() (which use ROW_NUMBER()) to get the desired effect.

EDIT: i experimented my self, the SetMaxResults and SetFirstResult are ignored on sql generation for ExecuteUpdate() so this will not work.

Also @ the comment below, an HQL ExecuteUpdate (and at least for DELETE) does not necessarily mean an in-memory load of object state.

I would use plain SQL for this. Mainly because of "I don't like to load all of them in memory". This is not a very object oriented task, so you don't need an ORM.

Note: plain SQL means that the session cache gets broken. You don't get any troubles as long as you don't do other stuff within the same (non-stateless) session. If you do, I would choose the OO way and actually load the items into memory.

I would use a combination of HQL's ExecuteUpdate and Desc ordering, similar to:

delete Person p1 
where p1.Id in 
            (select p2.Id 
             from Person p2 
             where rownum <= 10
             order by p2.Id desc)