Solved

get last record of a table

Posted on 2004-08-21
13
8,004 Views
Last Modified: 2008-09-24
i want to get last record of a table
and also
last no of records of a table

suggest me the query
thanks

raj
 
 
 
0
Comment
Question by:rajen_thakur
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +3
13 Comments
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 11858649
0
 

Expert Comment

by:pi_r
ID: 11864270
There is NO order in a table in a RDBS (this is the theory and all RDBS complies with this).
There is always, of course, an order, but it depends on how the system is implemented and it is not reacheable with a classic SQL query.

Here's a solution in pure SQL.
If you have a column with a number increased each time you add a row then (say idxnum), you can try something like :

select *
from tablename T1, tablename T2
where T1.idxnum = max(T2.idxnum);

or

select *
from tablename
where idxnum = (select max(idxnum)
                           from tablename);


I'm not sure for the syntax since I didn't write queries for a long time. It also depends if your version of Oracle allows embeded SQL queries.

idxnum can be implemented automatically with a trigger on insert.

For the last number of a record, as I said, there is no order so ...
But you can know the number of rows in a table:

select count(*)
from tablename;

or, with idxnum :
selcet max(idxnum)
from tablename;
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 11868697
@@Raj,

Is there a column in the table, which would help in identifying the last row ? Typically you would have an audit column (like CREATE_DATE and/or UPDATE_DATE), or a sequence number. You would be able to identify the last record using the values in these columns. In case you don't have any such column(s), it would be impossible to identify the last row (or rows). Most people get confused with the ROWID and/or ROWNUM, and believe that they would help in identifying the order of records inserted in the table. Unfortunately that is not true, and you would not be able to identify the order of records (insertion) using these Columns.

Hope this helps !
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 1

Expert Comment

by:luttappi123
ID: 11891154
Create a sequence. Use this sequence to insert value into a column which can be used to identify the last inserted column.

for eg:
 
Create table t (x int, y int);
create sequence x_seq;
insert into t values (x_seq.nextval, 1);
select * from t where x = (select max(x) from t);


Cheers
Luttappi
0
 
LVL 1

Expert Comment

by:erhanyayli
ID: 11900496
You can use an analytic function

select iv1.*, row_number() over (order by rowid desc) ord, count(*) over () rowcnt
where ord=1

Regards,
Erhan
0
 
LVL 2

Author Comment

by:rajen_thakur
ID: 11909813
Hi Erhan

The query u provided gives error
from keyword not found

i an using oracle 9i,

pls clearify me the query

raj
0
 
LVL 1

Expert Comment

by:luttappi123
ID: 11909848
Hi,
   If you requirement is only to find the last inserted record the following querry will work fine for u -

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

cheers
Luttappi
0
 
LVL 2

Author Comment

by:rajen_thakur
ID: 11909872
but u can not believe on rowid that max of rowid will give u last record.

rowid is something else for block file etc..
0
 
LVL 1

Expert Comment

by:luttappi123
ID: 11909917
ya..u r right...sorry for the mistake...i think the viable option is to add another field, increment it with sequence no and and use this field to get the last inserted record (may also use sysdate for the same)

Luttappi
0
 
LVL 1

Expert Comment

by:erhanyayli
ID: 11910221
Hi Rajen

"select iv1.*, row_number() over (order by rowid desc) ord, count(*) over () rowcnt
where ord=1"

Above example is missing from keyword

Try this one: Replace t1 with your table name

select * from
(
select iv1.*, row_number() over (order by rowid desc) ord, count(*) over () rowcnt
from t1 iv1
)
where ord=1

0
 
LVL 1

Accepted Solution

by:
luttappi123 earned 50 total points
ID: 11910345
hi erhanyayil,

    if rowid is used to retrieve the last value it will not be give the correct answer.

create table t1 (x int);

begin
for i in 1..100000
loop
    insert into t1 values(i);
end loop;
end;
/

select * from
(
select iv1.*, row_number() over (order by rowid desc) ord, count(*) over () rowcnt
from t1 iv1
)
where ord=1;


         X        ORD     ROWCNT
---------- ---------- ----------
       660          1     100000

clearly the correct value of x should be 100000 and not 660


SQL> select * from t1 where x = (select max(x) from t);

         X
----------
    100000


Rgds
Luttappi
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Database Design Dilemma 6 71
Cannot connect to Oracle database, python not recognizing cx_Oracle 2 70
error in oracle form 11 53
Oracle perfomance tuneing. 3 47
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question