Re: Limit a result list

From: Joerg Banholzer <oracle_at_banholzer.de>
Date: Tue, 3 Dec 2002 16:32:31 +0000 (UTC)
Message-ID: <b1cc025488d808c9bbac51ca3d122da1.116041@mygate.mailgate.org>

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

Received on Tue Dec 03 2002 - 10:32:31 CST