Fixed
Details
Details
Assignee
Unassigned
UnassignedReporter
Gregory Limoratto
Gregory LimorattoComponents
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
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 ?