Re: Sorted results not expected

[Larry Meadors]

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 <= #endRow#
                         order by lastupdatetime desc)
       WHERE rno >= #startRow#
       order by lastupdatetime desc

Larry


On 3/22/07, Tony Jang <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> wrote:
> > Probably the subselect, move that to the outer select statement and
> > see if it works.
> >
> > Larry
> >
> >
> > On 3/22/07, Tony Jang <[EMAIL PROTECTED]> 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 : [EMAIL PROTECTED]
Yhoo: [EMAIL PROTECTED]
ICQ : 231928022
http://blog.csdn.net/javafuns
http://blogger.org.cn/java2guru
http://blog.baturu.com