Última linha inserida

[tfg]

Meus amigos...
Como eu faço pra buscar a última linha inserida numa tabela???

Obrigado!!!
tfg

[dr_gori]

A única forma de ter o último registro, é colocando um campo com a DATA/HORA.

Na coluna do Tom Kyte da ORACLE MAGAZINE de Setembro/2001 tem um bom exemplo!!! Não funciona colocar MAX(ROWID), como muitos alegam...

Aqui está o LINK com vários exemplos.
CLIQUE AQUI

não FUNCIONA ASSIM:

Selecionar tudo

select * from emp where rowid=((select max(rowid) from emp));

[dr_gori]

Aí vai o texto do link acima:

Selecionar tudo

You Asked

I was wondering if there is some possible way that I can fetch the last record from a 
table. In this particular case, obviously one is unaware of the number of records in a 
table. All I want is to write a query whcih will simply fetch all the columns of the last 
record. I have tried using ROWNUM, but I guess it does'nt work that way. If I generate a 
report, then I want to base my report on the same query whcih will simply fecth all the 
columns of the last record.

Are there any built-ins in report builder which can fetch the last record of a table?

Thanks


Hassan 


and we said...

What is your definition of the last record?  

You know, the last record inserted may very well be the first record returned by "select 
* from t", it may be the 100'th, it may be the 1000'th, it may in fact be ANY record.

Records are not returned in order of anything.

If you want the last record inserted, you need to have a timestamp or sequence number 
assigned to each record as they are inserted, then:


select * from t where TIMESTAMP_COLUMN = ( select max(timestamp_column) from T )
and rownum = 1;


will get the "last" record.  It is the ONLY way.

 followup to the first 6 comments below

Proof by example (this proof shows all of the suggestions below are WRONG - the ONLY way 
to do this is to have some column you can order on to find the "last" row):

If ANY of these approaches worked -- the answer would be 4.  It is NEVER 4 in the 
following example:

tkyte@TKYTE816> create table t ( x int, a char(2000), b char(2000), c char(2000) );

Table created.

tkyte@TKYTE816>
tkyte@TKYTE816>
tkyte@TKYTE816> insert into t (x) values ( 1 );

1 row created.

tkyte@TKYTE816> commit;

Commit complete.

tkyte@TKYTE816> insert into t (x,a,b,c) values ( 2, 'x', 'x', 'x' );

1 row created.

tkyte@TKYTE816> commit;

Commit complete.

tkyte@TKYTE816> insert into t (x,a,b,c) values ( 3, 'x', 'x', 'x' );

1 row created.

tkyte@TKYTE816> commit;

Commit complete.

tkyte@TKYTE816> delete from t where x = 2;

1 row deleted.

tkyte@TKYTE816> commit;

Commit complete.

tkyte@TKYTE816> insert into t (x,a) values ( 4, 'x' );

1 row created.

tkyte@TKYTE816> commit;

Commit complete.

tkyte@TKYTE816>
tkyte@TKYTE816>
tkyte@TKYTE816> select x from t where rowid in(select max(rowid) from t);

         X
----------
         3

tkyte@TKYTE816>
tkyte@TKYTE816> select x from t where rownum = 1 order by rowid desc;

         X
----------
         1

tkyte@TKYTE816>
tkyte@TKYTE816> select x from ( select * from t order by rowid desc ) where rownum = 1;

         X
----------
         3

tkyte@TKYTE816>
tkyte@TKYTE816> select x from t minus select x from t where rownum < (select count(*) 
from t);

         X
----------
         3

tkyte@TKYTE816>
tkyte@TKYTE816>


Trust me on this one -- there is exactly ONE way to sort of get the last row 
inserted.  You MUST have some column that you can order by.

Rowid - won't work.  Its based on flie/block/slot.  We reuse rowids, we can even change 
them (partitioned tables, IOTS).  You might have extent #1 in file 55 and extent #2 in 
file 2.   Extent 4 might start on block 555 of file 3, Extent 5 might start on block 2 of 
file 3.  Rowids are NOT sortable.

Rownum -- doesn't work either, it just doesn't work.