Ashish Prasad

• Mar 27th, 2007

 

There is no native methods to fetch recently updated records as the prime functionality for an RDBMS is to store records in best way that makes prefeching faster, so storage is not based on update order but other attributes that makes DBMS fast

If you want to attain the functionality for getting recent record, yo need to create another column in the table that stores the timestamp of the records insterted. Based on that you can filter your records based on the new column data.e.g :SELECT * from ORD O1 where O1.row_insrt_ts > '2006-12-03 00:00:00'

Nitesh Srivastava

• Sep 14th, 2011

 

You can execute the following query and get the latest updated record

SELECT *
FROM emp e
WHERE ROWID IN (SELECT MAX (ROWID)
FROM emp e1)

Mark Haynes

 Profile Answers by Mark Haynes Questions by Mark Haynes

• Sep 21st, 2011

 

The problem with the question is it is too vague. How recent is recently UPDATED? The past millisecond? second? minute? hour? day? week? month? year? If you are working on Tables in a RDBMS, be it Oracle or SQL-Server or MySQL or DB2 or any others, one should always assume that you're not alone in your access of and work on a Table.

Say you have a Table, call it T1, which has a timestamp column in it, call it LastModified, and you're hoping to get the last Update base on SELECT statement pulling back the most recent record based on the LastModified column. Sounds simple, but you are not the only person doing UPDATES and INSERTS in the table. By the time you're ready to run your SELECT statement somebody else has also done a UPDATE, it's not your UPDATED record that's returned, it's their UPDATED record.

Well, what about using ROWID, well that won't work either since ROWID doesn't change. The only times ROWID will change are:

Import/Export of the table
Altering the Table by MOVE, SHRINK SPACE, or FLASHBACK
Splitting or Combining partitions
Update a value such that it moves the record into a new partition

I know at this point you may be jumping on the "Update a value such that it moves the record into a new partition" but you don't want to be doing that with every Update, thus that will be a very unlikely event.

So, you need to ask, What do you mean by most recent? and What is the scope of the most recent Updates, is it limited to only one user on a table or all users on a table?

SELECT *
FROM Update_Table UT1
WHERE UT1.Modified_Date = (SELECT MAX(UT2.Modified_Date)
FROM Update_Table UT2
WHERE UT2.PK = UT1.PK
AND UT2.Last_User = user)

This query assumes that every user to the database has a individual user id to access the database, and not some generic id that everybody who access the database used.

leelakrishna302

 Profile Answers by leelakrishna302 

• Jun 15th, 2012

 

select * from emp e where rowid in( select max(rowid) from emp );

Ayyappa

• Aug 24th, 2012

 

Hello leelakrishna302 ...

Your query ...

Code
 


SELECT * FROM emp e 


WHERE rowid IN (SELECT max(rowid) FROM emp); 


 

gives only latest inserted records not the latest updated records..