Problem using distinct query with SetMaxResult

Description

Dialect: NHibernate.Dialect.MsSql2005Dialect
dotNet Framework 2.0
SQL Server 2005

When I put the 'distinct' keyword into the HQL statement and then use SetMaxResult / SetFirstResult, the 'ROW_NUMBER()' in the generated query causes interference with the 'distinct' :

[code]
string hql = "select distinct item from MyTable item"
IQuery query = session.CreateQuery(hql);
query.SetFirstResult(0).SetMaxResults(10).List();
/code

the problem is that the ROW_NUMBER is use by the distinct.

I've modified the NHibernate.Dialect.MsSql2005Dialect to generate something like this:

SELECT
TOP last (columns)
FROM
(SELECT (__T.columns), ROW_NUMBER() OVER(ORDER BY {original order by, with un-aliased column names) as __hibernate_sort_row
FROM (
SELECT (columns) FROM {original from}
) as __T
) as query
WHERE query.__hibernate_sort_row > offset
ORDER BY query.__hibernate_sort_row

It works, but can you include this feature in your next release ?

Environment

None

Activity

Show:
Fixed

Details

Assignee

Reporter

Components

Fix versions

Affects versions

Priority

Who's Looking?

Open Who's Looking?
Created February 13, 2009 at 2:37 AM
Updated August 13, 2016 at 10:29 AM
Resolved February 20, 2009 at 8:11 PM
Who's Looking?