Re: Display last records in a table

Good god! You're right. Sorry - there is no way to do that in Oracle. Too bad.... Try another DBMS.

Or...

I was making the assumption that someone just wanted to look at some recently added records, they didn't have a timestamp, or a sequence numbered field available. They didn't want to do a dump of the whole table to look at the later records (In real life, I've actually seen this done - believe it or not!) This was just a quick way of looking at the bottom of a dump. Since they chose the number 10, and not records added since a certain date, it sounded a little arbitrary.

I know that Oracle does not guarantee where it will store a given record. I like the timestamp field too. But, I often find myself trying to offer some solution in real life situations rather than tell people 'You can't do that...' It's a little like telling people who ask you for directions that they should use the map they should have packed. If they don't have the map, they obviously can't use it, they still need to get there somehow.

Happy Tuesday.

On Sat, 26 May 2001 20:10:56 -0400, Thomas Kyte <tkyte_at_us.oracle.com> wrote:

>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
>>>
>>>
Received on Tue May 29 2001 - 17:47:53 CDT