Re: selecting the last 10 rows...

John Strange schrieb:
>
> Ok, is this any better
>
> select whatever
> from where_ever
> where rownum > (select max (rownum) - 10
> from where_ever
> )
> order by ordered_column descending

Shouldn't Jurij Modic's solution fit?

  SELECT /*+ RULE */ emp.* FROM emp, dual   WHERE emp.sal = DECODE(dual.dummy(+),'X',NULL,NULL)   AND rownum <= 10
  ORDER BY emp.sal DESC;

>
> 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.

--
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Mon Nov 30 1998 - 03:38:28 CST