Última linha inserida

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Mensagemem Qui, 06 Jan 2005 10:39 am

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

Obrigado!!!
tfg
tfg
Localização: Novo Hamburgo - RS


Mensagemem Qui, 06 Jan 2005 10:45 am

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:
Código: Selecionar todos
select * from emp where rowid=((select max(rowid) from emp));
Editado pela última vez por dr_gori em Seg, 12 Fev 2007 9:27 am, em um total de 1 vez.
dr_gori
Localização: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Mensagemem Seg, 12 Fev 2007 9:26 am

Aí vai o texto do link acima:

Código: Selecionar todos
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.




dr_gori
Localização: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered


  • Veja também
    Respostas
    ExibiÇões
    Última mensagem


    Voltar para SQL

    Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 6 visitantes