# Cannot :order when using :offset and :limit in find **URL:** \ **Category:** Rails\ **Created:** [January 25, 2006, 5:09pm UTC](https://www.ruby-forum.com/t/cannot-order-when-using-offset-and-limit-in-find/97270 "2006-01-25T17:09:57Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![jjantz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/dfb087/32.png) [@jjantz](https://www.ruby-forum.com/u/jjantz)\ **Post date:** [January 25, 2006, 5:09pm UTC](https://www.ruby-forum.com/t/cannot-order-when-using-offset-and-limit-in-find/97270/1 "2006-01-25T17:09:57Z")
Hello all. I am using the Paginate\_with\_ajax code as described on the wiki but I am running into the following problem. The following code will work fine and retrieved unsorted records in a hunky dory fashion: @componentlogs = Componentlog.find(:all, :conditions =\> [“cl\_compname like ?”, @criteria], :offset =\> offset, :limit =\> items\_per\_page ) However if I try and add an order… @componentlogs = Componentlog.find(:all, :conditions =\> [“cl\_compname like ?”, @criteria], :offset =\> offset, :limit =\> items\_per\_page, :order =\> “cl\_spr DESC” ) It goes totally doolally. OCIError: ORA-00907: missing right parenthesis: select \* from (select raw\_sql\_.\*, rownum raw\_rnum\_ from (SELECT \* FROM componentlog WHERE (cl\_compname like ‘CAPODIR’) ORDER BY cl\_spr DESC ) raw\_sql\_ where rownum \<= 25) where raw\_rnum\_ \> 0 I don’t have much control over this (line is there, line isn’t, line is there, line isn’t…) and would rather not resort to using a find\_by\_sql but if I have to I will. I am running on oracle using the oci driver Any help would be much appreciated! Thanks Jeff ---
**Author:** ![jjantz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/dfb087/32.png) [@jjantz](https://www.ruby-forum.com/u/jjantz)\ **Post date:** [January 25, 2006, 8:43pm UTC](https://www.ruby-forum.com/t/cannot-order-when-using-offset-and-limit-in-find/97270/2 "2006-01-25T20:43:29Z")
My guess is it is a problem with the Oracle adapter. I was able to use all those options together on MySQL. But, then again, that’s probably not much help because the syntax in MySQL is different :). -Jonny. Jeff J. wrote: > It goes totally doolally. > > OCIError: ORA-00907: missing right parenthesis: select \* from (select > raw\_sql\_.\*, rownum raw\_rnum\_ from (SELECT \* FROM componentlog WHERE > (cl\_compname like ‘CAPODIR’) ORDER BY cl\_spr DESC ) raw\_sql\_ where > rownum \<= 25) where raw\_rnum\_ \> 0 > > I don’t have much control over this (line is there, line isn’t, line is > there, line isn’t…) and would rather not resort to using a find\_by\_sql > but if I have to I will. > > I am running on oracle using the oci driver ---
**Author:** ![jjantz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/dfb087/32.png) [@jjantz](https://www.ruby-forum.com/u/jjantz)\ **Post date:** [January 26, 2006, 11:01am UTC](https://www.ruby-forum.com/t/cannot-order-when-using-offset-and-limit-in-find/97270/3 "2006-01-26T11:01:32Z")
Jonathan V. wrote: > My guess is it is a problem with the Oracle adapter. I was able to use > all those options together on MySQL. But, then again, that’s probably > not much help because the syntax in MySQL is different :). > > -Jonny. > > Jeff J. wrote: > > > It goes totally doolally. > > > > OCIError: ORA-00907: missing right parenthesis: select \* from (select > > raw\_sql\_.\*, rownum raw\_rnum\_ from (SELECT \* FROM componentlog WHERE > > (cl\_compname like ‘CAPODIR’) ORDER BY cl\_spr DESC ) raw\_sql\_ where > > rownum \<= 25) where raw\_rnum\_ \> 0 > > > > I don’t have much control over this (line is there, line isn’t, line is > > there, line isn’t…) and would rather not resort to using a find\_by\_sql > > but if I have to I will. > > > > I am running on oracle using the oci driver Nutbunnies, I was afraid of that. Off to bug (har har, I kill myself) the guy who wrote the driver 😉 Jeff ---
**Author:** ![jjantz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/dfb087/32.png) [@jjantz](https://www.ruby-forum.com/u/jjantz)\ **Post date:** [January 26, 2006, 5:33pm UTC](https://www.ruby-forum.com/t/cannot-order-when-using-offset-and-limit-in-find/97270/4 "2006-01-26T17:33:56Z")
Hello, Jeff Can you run the following SQL on sqlplus? select \* from (select raw\_sql\_.\*, rownum raw\_rnum\_ from (SELECT \* FROM componentlog WHERE (cl\_compname like ‘CAPODIR’) ORDER BY cl\_spr DESC ) raw\_sql\_ where rownum \<= 25) where raw\_rnum\_ \> 0 I guess that your Oracle server version is 8.0, which doesn’t support order-by clause in a subquery. ---
**Author:** ![jjantz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/dfb087/32.png) [@jjantz](https://www.ruby-forum.com/u/jjantz)\ **Post date:** [January 26, 2006, 5:42pm UTC](https://www.ruby-forum.com/t/cannot-order-when-using-offset-and-limit-in-find/97270/5 "2006-01-26T17:42:07Z")
KUBO Takehiro wrote: > Hello, Jeff > > Can you run the following SQL on sqlplus? > > select \* from (select > raw\_sql\_.\*, rownum raw\_rnum\_ from (SELECT \* FROM componentlog WHERE > (cl\_compname like ‘CAPODIR’) ORDER BY cl\_spr DESC ) raw\_sql\_ where > rownum \<= 25) where raw\_rnum\_ \> 0 > > I guess that your Oracle server version is 8.0, which doesn’t support > order-by clause in a subquery. Hi Takehiro. Ahhhh right, yes you are correct. Tt doesn’t work in SQL plus either. I was under the impression that it was a problem with the adaptor or the offset / limit bit. Is there a way to mimick that sort of functionality using a hackish approach? Manys thanks for the clarification and I suppose you can delte with extreme prejudice that bug I raised on the rubyforge page 🙂 Jeff