ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Joakim Olsson" <argo...@lingonpaj.com>
Subject Re: Sorted results not expected
Date Fri, 23 Mar 2007 06:52:49 GMT
Actually this will probably give the wrong result as well (if it gives the
correct result it's only by pure luck).

What Oracle does in this case is to get the rows from the inner select
based on the criterias and then sort them according to the order by
clause. The result is that the ROWNUM < #endRow# happens before the sort
which is probably not what you want.

See this article at AskTom for a proper way to solve it:
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

Regards,
Joakim


> Swell, it's still wrong.
>
> This will work.
>
> SELECT *
>                FROM (select rownum rno, a.a aa, a.b
>                             ab, a.c ac, b.a ba, b.b bb, b.c bc, b.d bd,a.d
> ad,
>                             a.lastupdatetime lastupdatetime, d.a da
>                           from table1 a, table2 b, table3 c, table4 d
>                           where a.id = c.id and a.x = 1
>                                and a.y = 1 and c.z = 1
>                                and a.sid = b.sid and a.id = #custID#
>                                and a.sid = d.sid and ROWNUM &lt;= #endRow#
>                           order by lastupdatetime desc)
>         WHERE rno &gt;= #startRow#
>         order by lastupdatetime desc
>
> Larry
>
>
> On 3/22/07, Tony Jang <guangquanzhang@gmail.com> wrote:
>> well, i did not describe in detail: my sql works well in PL/SQL
>> developer, all results are sorted as expected, but not correctly in my
>> program
>>
>>
>> On 3/22/07, Nathan Maves <nathan.maves@gmail.com> wrote:
>> > Well it looks to me like you are trying to duplicate the built in
>> method to
>> > select a specified number of rows from a select.  If that is the case
>> I
>> > would look into using the partition clause in your sql statement.
>> This is
>> > an Oracle specific command and you can just google for it.
>> >
>> > Nathan
>> >
>> >
>> > On 3/22/07, Larry Meadors <lmeadors@apache.org> wrote:
>> > > Probably the subselect, move that to the outer select statement and
>> > > see if it works.
>> > >
>> > > Larry
>> > >
>> > >
>> > > On 3/22/07, Tony Jang <guangquanzhang@gmail.com> wrote:
>> > > > hi all
>> > > >     I used ORDER BY to get sorted results, but why is it not my
>> expect
>> > results?
>> > > >     My database is ORACLE 9.
>> > > >     Any help will be appreciated
>> > > >
>> > > >     Below is my sql statement:
>> > > > SELECT *
>> > > >                 FROM (select rownum rno, a.a aa, a.b
>> > > >                              ab, a.c ac, b.a ba, b.b
>> > bb, b.c bc, b.d bd,a.d ad,
>> > > > a.lastupdatetime lastupdatetime, d.a da
>> > > >                            from table1 a, table2 b,
>> > table3 c, table4 d
>> > > >                            where a.id = c.id and a.x =
>> > 1
>> > > >                                 and a.y = 1 and c.z = 1
>> > > >                                 and a.sid = b.sid and
>> > a.id = #custID#
>> > > >                                 and a.sid = d.sid and
>> > ROWNUM &lt;= #endRow#
>> > > >                          order by lastupdatetime desc)
>> > > >          WHERE rno &gt;= #startRow#
>> > > >
>> > >
>> >
>> >
>>
>>
>> --
>>                 guangquanzhang AT BeiJing
>>
>> -----contact info------
>> MSN : guangquanzhang@hotmail.com
>> Yhoo: z_guangquan@yahoo.com
>> ICQ : 231928022
>> http://blog.csdn.net/javafuns
>> http://blogger.org.cn/java2guru
>> http://blog.baturu.com
>>
>
>


Mime
View raw message