Re: Oracle Queries/Paging

From: Igor Faershtein <faershtein_at_usa.net>
Date: Mon, 18 Oct 1999 16:21:46 +0400
Message-ID: <7uf79t$eqo@komitex.komitex.ru>

As you remember, the needed subj was with the next demand :
>we would need the flexibility to order the
>nested query (if only rownum's were assigned after the ORDER BY)

here is the nested query

( select rownum rn, id,code from komioil.prim3   group by code,id, rownum ) A

as you can see we order by CODE first .....next and so on using only the GROUP BY clause
hire is no ORDER BY clause !
(but we have got ordered resultset from this query, haven't we ?)

now we use this result in the root query

---

select rownum , A.*
  from

  ( select rownum rn, id,code from komioil.prim3

       group by code,id, rownum ) A

  group by rownum, a.RN,a.ID,a.CODE
  having rownum >=5 and rownum <8

---

as you can see, no ORDER BY clause is used here !

we make grouping of the ordered resultset

> group by rownum, a.RN,a.ID,a.CODE

only for getting rows we need by HAVYNG

> having rownum >=5 and rownum <8

here > group by rownum, .....

this "rownum" field - is the rownum of the root resultset, but not nested !

I think You mixed my last message with previous. There ,really, was an error with ORDER BY, but I have not read the demands so well before I answered the message

Igor

Dmitry E. Loginov
> In this example ORDER BY clause works _AFTER_ selecting by rownum, that
> made invalid sorting.
>
> Igor Faershtein wrote:
> >
> > I'm sorry, If you need to order first you can try the next way
> >
> > select rownum , A.*
> > from
> > ( select rownum rn, id,code from komioil.prim3
> > /*where clause*/
> > group by code,id, rownum /* ordering by code first, id next and the
last
> > by rownum */
> > /*having clause */ ) A
> > /*****************/
> > /* where clause*/
> > group by rownum, a.RN,a.ID,a.CODE
> > having rownum >=5 and rownum <8
> >
> > /*order clause */
> >
> > Igor
Received on Mon Oct 18 1999 - 07:21:46 CDT