Re: Limit a result list

the rownum is assigned before the order by

Joerg Banholzer wrote:

> Thanks a lot. But I have (for me) strange results. The column rn is not
> ordered from 1 to max. Is ROWNUM the number of the unordered result?
>
> I try to show what I mean: My result now looks like:
>
> entrydate              somethingelse    nr
> ---------------------------------------------
> 2002-12-01 16:00:00       asdfasdf      18
> 2002-12-01 15:00:00       lkjasdfl       7
> 2002-12-01 14:00:00       iornkrrr      22
>  .
>  .
>  .
>
> Can I do what I want with another subselect like:
>
>  select * from
>  (
>    select t.*, ROWNUM rn
>    (
>      SELECT t.* FROM tablename t WHERE something
>      ORDER BY entrydate
>    )
>  )
>  where rn between 30 and 40;
>
> That gives a different result than the Query below and still not the
> same like the "MINUS-STATEMENT" in the original message which also
> works in some way?
>
> "Alexander Zimmer" <zimmer_at_hollomey.com> wrote in message
> news:MPG.1856e8aa74b3ab429896cf_at_news.cis.dfn.de
>
> > Do it like this:
> >
> >   select * from
> >   (
> >     SELECT t.*, ROWNUM rn FROM tablename t WHERE something
> >     ORDER BY entrydate
> >   )
> >   where rn between 30 and 40;
> >
> > It does not work without the subselect, because ROWNUM would never
> > exceed 1:
> >
> > Consider the following select:
> >
> >   SELECT t.*, ROWNUM rn FROM tablename t WHERE something ORDER BY
> >   WHERE ROWNUM between 30 and 40;
> >
> > Fetch first row. Rownum between 30 and 40? No, it is 1.
> > Fetch second row. Rownum between 30 and 40? No, it is STILL 1 (because
> > this would be the FIRST row to be fetched).
> > Fetch third row. Rownum between 30 and 40? No, it is STILL 1.
> >
> > Thus, there would never be a row returned. You have to select the rownum
> > first and THEN subquery and "where" it.
> >
> > hth
> > Alex
> >
> >
> >
> > oracle_at_banholzer.de tipperte...
> > > I have the following problem:
> > >
> > > How can I get the results (for example 30 - 39) from a select statement?
> > > The result list should by ordered by a date.
> > >
> > > With MySQL I did it like :
> > >
> > > SELECT * FROM tablename WHERE something ORDER BY entrydate LIMIT 30, 10
>
> --
> Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

--
Suzuki SV650S - plop.  Gone.
Kwak ZX-6R J2 - hear the roar