Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Selecting last n rows

Re: Selecting last n rows

From: Roy Brokvam <roy.brokvam_at_conax.com>
Date: Wed, 30 Jun 1999 12:13:21 +0200
Message-ID: <55me3.123$Gx.228@news1.online.no>

Vincent Lemaire wrote in message <7lcm92$rhj$1_at_minus.oleane.net>...
>I think you should try with the rownum pseudocolumn
>something like :
>
>select * from the_table
>where rownum >= (select max(rownum)-10 from the_table);
>
>if it is what you expected...?
>
>

Vincent, please try your example before posting. The pseudo-column Rownum gets its value when the row is selected. The first returned row gets rownum 1, the second gets rownum 2, and so on. Also, if it worked, it would have returned 11 rows, not 10.

Let's say you want the rows with rownum between 5 and 10. That is not possible! The "first" row which otherwise satisfies the where-clause would have gotten rownum = 1, and hence will be rejected. The next one would also have gotten rownum = 1 (because no rows are returned yet), hence it will be rejected. And so on.

Himanshu, what do you mean by "last n?". There are several plausible meanings of "last n":

* The n rows with largest binary representation of rowid
* The n rows which were last created in the database
* The n rows with the "largest" primary keys (define "largest")
* The n rows with the "largest" value for some column (define "largest")

My guess is that your definition is the second one. In that case, you should have an id column, which gets its value from a sequence, in the table.

You cannot do this:
SELECT * from the_table WHERE rownum <= 10 ORDER BY id DESC , because rownums are assigned before the result set is sorted.

You simply will get 10 non-deterministically selected rows with decreasing ids.

Also, you should not do this:
SELECT * from the_table WHERE id > (SELECT max(id) - 10 FROM the_table) , since sequence numbers may be "lost"

Mark's code will work (if you replace 10000 with 10), but it will be horribly slow on large tables. In SQL*Plus, I can't come up with anything better, though.

Other suggestions:
* Is your question a quote from your task? Or is it one of perhaps several approaches to solving your problem? Consider whether your problem can be rephrased.
* Check whether your client supports a restriction on the number of returned rows (like Oracle*Reports does).
* Consider using PL/SQL explicit cursors.

Best regards,

Roy Brokvam
roy.brokvam_at_conax.com Received on Wed Jun 30 1999 - 05:13:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US