Re: selecting the last 10 rows...

Hi,
Not quite ...
Rownum has NOTHING to do with the order by clause. So using to it to restrain it makes no sence.

Regards
  Peter Larsen

John Strange wrote in message <73sg98$b4v$1_at_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 Mon Nov 30 1998 - 11:00:19 CST