I would still add a order by to the query, just because the data is the same doesn't guarantee that the results will be the same...
From: [email protected] [mailto:[email protected]] On Behalf Of wsadiq Sent: 11 February 2013 14:45 To: [email protected] Subject: Re: [nhusers] Issue with SetFirstResult and SetMaxResults I am getting the same result with 3.3.1 as well as 3.2.0. Thanks. On Wednesday, February 6, 2013 11:29:04 PM UTC-6, Alexander I. Zaytsev wrote: What version of NHibernate are you using? Also, you missed ordering in query. To get correct paged results query must be ordered. Best Regards, Alex 2013/1/30 unhaddins <[email protected] <javascript:> > I am executing the same criteria against SQLServer and Oracle and get two differen tresults. the data in both databases is exactly the same. First, here is my code. ICriteria crit = CurrentSession.CreateCriteria(typeof(T)); crit.SetFirstResult(3); crit.SetMaxResults(1); IList<T> set = crit.List<T>(); I am wanting to get 1 row starting from record 4 (assuming 0 base). That is exactly the result I get from SQL Server but the query generated for Oracle returns 0. Here are the queries generated by nHibernate captured through NHProfiler. SQL SERVER QUERY SELECT TOP (1 /* @p0 */) Id64_0_, Code64_0_, Name64_0_, Targeted64_0_, StartDate64_0_, EndDate64_0_, DisplayO7_64_0_, CreateDate64_0_, UpdateDate64_0_ FROM (SELECT this_.Id as Id64_0_, this_.Code as Code64_0_, this_.Name as Name64_0_, this_.Targeted as Targeted64_0_, this_.StartDate as StartDate64_0_, this_.EndDate as EndDate64_0_, this_.DisplayOrder as DisplayO7_64_0_, this_.CreateDate as CreateDate64_0_, this_.UpdateDate as UpdateDate64_0_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM LW_Promotion this_ WHERE this_.Id in (2 /* @p1 */, 4 /* @p2 */, 5 /* @p3 */, 6 /* @p4 */, 7 /* @p5 */, 8 /* @p6 */)) as query WHERE query.__hibernate_sort_row > 3 /* @p7 */ ORDER BY query.__hibernate_sort_row ORACLE QUERY select Id64_0_, Code64_0_, Name64_0_, Targeted64_0_, StartDate64_0_, EndDate64_0_, DisplayO7_64_0_, CreateDate64_0_, UpdateDate64_0_ from (select row_.*, rownum rownum_ from (SELECT this_.Id as Id64_0_, this_.Code as Code64_0_, this_.Name as Name64_0_, this_.Targeted as Targeted64_0_, this_.StartDate as StartDate64_0_, this_.EndDate as EndDate64_0_, this_.DisplayOrder as DisplayO7_64_0_, this_.CreateDate as CreateDate64_0_, this_.UpdateDate as UpdateDate64_0_ FROM LW_Promotion this_ WHERE this_.Id in (44 /* :p0 */, 162 /* :p1 */, 165 /* :p2 */, 168 /* :p3 */, 171 /* :p4 */, 174 /* :p5 */)) row_ where rownum <= 1 /* :p6 */) where rownum_ > 3 /* :p7 */ Can anybody shed some light as to what is nHobernate tryign to do with the oracle query. Thanks in advance. Waqar Sadiq -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <javascript:> . To post to this group, send email to [email protected] <javascript:> . Visit this group at http://groups.google.com/group/nhusers?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
