On Fri, 21 May 1999 pgrossi@lycosmail.com wrote:

> Hi Ari,
>
> I'm trying to select the last n records stored on a table but I didn't find
> until now a solution to my problem. Could you help me ?
> I tried using rownum way but I don't know if it's the right and/or the best
> way.> > Thanks in Advance> >    PietroPietro,

ROWNUM can only be used to select records from the "front" of the order of
records being returned.

To find the "last" n records depends on what you mean by "last". Oracle
returns records randomly (actually by the way they are stored) unless
there is an ORDER BY CLAUSE.

If this is the order, then you can make a PL/SQL routine. Create a cursor
with any ORDER BY CLAUSES (not mandatory). Then make a counter variable.
When you select a record, increment the counter. After the counter
surpasses a threshold, start returning records (DBMS_OUTPUT.PUT_LINE).

Hope that this helps!

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 295+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->


Hi ari and Pietro:
   I'm a chiness DBA. I work for a Medical University.
   I think I have found a easy way to select last n record from a table.
below is my SQL statement:

select * from (select rownum a, column1,column2 ,...,columnn from my_table)
where a > ( select (max(rownum)-10) from my_table);
    

     where my_table is the sample table's name ;
           column1 to columnn  are the  columns in the table,
         you can replace 10 as the n where you want to select;

As Mr ari said:
 the "last" n records depends on what you mean by "last". Oracle
returns records randomly (actually by the way they are stored) unless
there is an ORDER BY CLAUSE.
   If you want returns records you "last" means .Maybe You  have to use a 
cursor.
    I hope the solution can help you !
                                zhijie_stone
                               email:zhijie_stone@163.net


Back to Ari Kaplan's Home Page