Re: Display last records in a table

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 26 May 2001 20:10:56 -0400
Message-ID: <91h0ht4v0pjikdckrq8u8vp8scgno1a8o1@4ax.com>

A copy of this was sent to Michael Dodd <doddme_at_mindspring.com> (if that email address didn't require changing) On Sat, 26 May 2001 09:17:51 -0500, you wrote:

>SQL> create table test_dates (my_int number, my_date date);
>
>Table created.
>
>SQL> declare i number;
> 2 begin
> 3 for i in 1 .. 1000 loop
> 4 insert into test_dates values(i,sysdate+i);
> 5 end loop
> 6 ;
> 7 end;
> 8 /
>
>PL/SQL procedure successfully completed.
>
>SQL> select count(*) from test_dates;
>
> COUNT(*)
>----------
> 1000
>
>SQL> select max(my_date) from test_dates;
>
>MAX(MY_DA
>---------
>20-FEB-04
>
>SQL> select my_date from (select my_date,rownum from test_dates order
>by rownum desc) where rownum <
> 10;
>
>MY_DATE
>---------
>20-FEB-04
>19-FEB-04
>18-FEB-04
>17-FEB-04
>16-FEB-04
>15-FEB-04
>14-FEB-04
>13-FEB-04
>12-FEB-04
>
>9 rows selected.
>
>SQL>
>

that'll never work in real life. In my database, with an 8k block size:

ops$tkyte_at_ORA8I.WORLD> create table t ( x int, a char(2000) default 'x', b char(2000) default 'x', c char(2000) default 'x' );

Table created.

ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> begin

  2          for i in 1 .. 20 loop
  3                  insert into t ( x ) values ( i );
  4          end loop;
  5          commit;

  6 end;
  7 /

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA8I.WORLD> delete from t where x <= 10;

10 rows deleted.

ops$tkyte_at_ORA8I.WORLD> commit;

Commit complete.

ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> begin

  2          for i in 1 .. 10 loop
  3                  insert into t ( x ) values ( 100+i );
  4          end loop;
  5          commit;

  6 end;
  7 /

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> select x from (select x,rownum from t order by rownum desc)
  2 where rownum < 10;

         X

---

        20
        19
        18
        17
        16
        15
        14
        13
        12

9 rows selected.

That is definitely not the last inserted set of rows.

Unless a table has some timestamp that can be used to order by data sort of by order of insertion -- you cannot get the "last 10" -- there is no such concept in a relational database (there is no first 10, last 10, middle 10 -- there is just a heap of data).

If you have a timestamp or a sequence on that table then:

select *
  from ( select * from T order by TIMESTAMP DESC )  where rownum <= 10
/

will get the 10 records with the highest timestamps (in Oracle8i and up -- order by in a subquery is new with 8i).

>On Fri, 25 May 2001 17:04:59 +0200, "Thomas Stuefer"
><stuefer_at_halli-data.at> wrote:
>
>>How can i display the last records in a table with an select-statement ?
>>
>>For example:
>>
>>I have an table with many thousand of records and only want to display the
>>last 10 added records.
>>
>>Many thanks for your help !
>>
>>Tom
>>
>>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat May 26 2001 - 19:10:56 CDT