Re: selecting the last 10 rows...

From: John Strange <jstrange_at_imtn.dsccc.com>
Date: 29 Nov 1998 22:02:16 GMT
Message-ID: <73sg98$b4v$1@relay1.dsccc.com>

Ok, is this any better

select whatever
from where_ever
where rownum > (select max (rownum) - 10

                  from   where_ever
                )

order by ordered_column descending

 P. Larsen (petlars_at_fls.infi.net) wrote:
: you're wrong.
: ROWNUM gets assigned BEFORE sort.
: Your sort is hence random - the first 10 records fetched - not the first 10
: records after sort.

: The only way to select "number of rows" in an ordered list is using a host
: language or a PL/SQL cursor. By looping through the cursor, you stop the
: loop after X number of rows and close the cursor.

: You're right in the sence that if you want the 10 last rows you sort
: descending. But if you don't have an ordered list, you need to solve this
: problem programmically (buffing records, and when End of cursor is reached
: you print your 10 buffered records).

: - P. Larsen
: Senior Oracle Consultant

: John Strange wrote
: >If the table is truly ordered
: >
: >select whatever
: >from where_ever
: >where rownum < 11
: >order by ordered_column descending
: >
: >
: >Martin Trzaskalik (martin_at_ernie.mi.uni-koeln.de) wrote:
: >: ROWNUM offers a nice feature to select the first 10 rows from a
: >: ordered table.
: >
: >: Is there an easy way of selecting the last 10 rows from a ordered
: >: table?
: >
: >: Martin

--
While Alcatel may claim ownership of all my ideas (on or off the job), Alcatel does not claim any responsibility for them. Warranty expired when u opened this article and I will not be responsible for its contents or use. Received on Sun Nov 29 1998 - 16:02:16 CST