ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Larry Meadors" <lmead...@apache.org>
Subject Re: Sorted results not expected
Date Fri, 23 Mar 2007 02:53:45 GMT
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