rajen_thakur
asked on
get last record of a table
i want to get last record of a table
and also
last no of records of a table
suggest me the query
thanks
raj
and also
last no of records of a table
suggest me the query
thanks
raj
Have a look at http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:912210644860
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;
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;
@@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 !
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 !
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
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
You can use an analytic function
select iv1.*, row_number() over (order by rowid desc) ord, count(*) over () rowcnt
where ord=1
Regards,
Erhan
select iv1.*, row_number() over (order by rowid desc) ord, count(*) over () rowcnt
where ord=1
Regards,
Erhan
ASKER
Hi Erhan
The query u provided gives error
from keyword not found
i an using oracle 9i,
pls clearify me the query
raj
The query u provided gives error
from keyword not found
i an using oracle 9i,
pls clearify me the query
raj
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
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
ASKER
but u can not believe on rowid that max of rowid will give u last record.
rowid is something else for block file etc..
rowid is something else for block file etc..
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
Luttappi
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
"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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.