Re: Last 100 rows

The over arching question is what do you mean by the 'last' rows ...the storage of the rows in an Oracle table is not linear - that is, the last record entered is not necessarily stored at the 'end' of the existing rows ....if you mean the latest added, then use a time stamp for retrieval, if you mean the largest ( or smallest) use the where clause to select for it...

To get an arbitrary set of rows you can use the MINUS operator to do it:

select * from table where rownum < 301
MINUS
select * from table where rownum < 100

will result in rows 100 to 300 being returned...  

Mat Kelcey <matthew.kelcey_at_SPAMMENOT.nokia.com> wrote:

>isn't this going to be just as slow, since the sub query is a full select also?
>
>how do we extend this kind of query to work with not just the last rows but an
>arbitrary set of rows somewhere in the table?
>
>i can understand that with a sort the entire table needs to be scanned but with a
>plain select * why can't i get ask for row 100 to row 200 or something (skipping
>row's up to 100)? this is important when i want 100 rows from 999,600 to 999,700
>for example since i don't want to have to scan over the first 999,600.
>
>when an order by clause is added things get even worse!
>
>i just wish i could use scrolling result sets.. (but will have to wait until we
>have the server/driver combo to support it)
>
></mat>
>
>Carsten Jacobs wrote:
>
>> How about:
>> select * from
>> (select *
>> from huge_tab
>> order by order_col DESC)
>> where rownum < 101;
>>
>> Carsten
>>
>> sang schrieb:
>> >
>> > Hi ,all
>> >
>> > I want to get my last 100 rows data from my huge table ordered by date.
>> > and i want to get the last row too.
>> > I know i can Select * from hugetable and move to the last, but it will take
>> > much
>> > time for it.
>> >
>> > Has anyone a quick way?
>> >
>> > Thanks in advance
>>
>> --
>>
>> The information transmitted is intended only for the person or entity to
>> which it is addressed and may contain confidential and/or privileged
>> material. Any review, retransmission, dissemination or other use of, or
>> taking of any action in reliance upon, this information by persons or
>> entities other than the intended recipient is
>> prohibited. If you received this in error, please contact the sender and
>> delete the material from any computer.

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 80,000 Newsgroups - 16 Different Servers! =----- Received on Thu Jun 01 2000 - 00:00:00 CDT