422,904 Members | 1,030 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,904 IT Pros & Developers. It's quick & easy.

Retriving Last ten modified Records

P: 3
Hi All,

How can i select last ten modified records in table.

Can u please help me.
Feb 24 '07 #1
Share this Question
Share on Google+
18 Replies


vijaydiwakar
100+
P: 579
Hi All,

How can i select last ten modified records in table.

Can u please help me.
dear i think there is no such provision but u may do one thing create a trigger after update add one field lastmodified as date
this trigger will then auto save the current sysdate in that column and then ur able to retrive the data
try it
Feb 27 '07 #2

P: 4
Hi All,

How can i select last ten modified records in table.

Can u please help me.

Dear ,

Try rownum pseudocolumn to find out the last 10 records

like select rownum,ename,sal from emp;

Zafar Iqbal
Mar 2 '07 #3

P: 4
Hi All,

How can i select last ten modified records in table.

Can u please help me.

Hello Dear,

Try this

select rownum from emp
group by rownum
having rownum >=(select max(rownum)-10 from emp)

hope it works

Zafar Iqbal
Karachi
Mar 2 '07 #4

vijaydiwakar
100+
P: 579
Hello Dear,

Try this

select rownum from emp
group by rownum
having rownum >=(select max(rownum)-10 from emp)

hope it works

Zafar Iqbal
Karachi
no dear the rownum doesnot work properly or u may say in that manner in which ur expecting
to see how try to use subqry with rownum in both qry
Mar 2 '07 #5

P: 16
no dear the rownum doesnot work properly or u may say in that manner in which ur expecting
to see how try to use subqry with rownum in both qry

- Have you a date_modified column in your table?
if it's the case. Why you don't use this :

select * from
( select * from yourtable order by date_modif desc)
where rownum <= 10
Mar 2 '07 #6

P: 4
no dear the rownum doesnot work properly or u may say in that manner in which ur expecting
to see how try to use subqry with rownum in both qry
My query will retrieve last ten inserted record. I will try to
find last ten modified records.

Zafar Iqbal
Mar 3 '07 #7

P: 3
Rks
dear i think there is no such provision but u may do one thing create a trigger after update add one field lastmodified as date
this trigger will then auto save the current sysdate in that column and then ur able to retrive the data
try it
Thanks for your update vijay,

I try this one and get back to you once completed.

Thanks,
Ramesh S
Mar 3 '07 #8

P: 3
Rks
Hello Dear,

Try this

select rownum from emp
group by rownum
having rownum >=(select max(rownum)-10 from emp)

hope it works

Zafar Iqbal
Karachi

Can u plz explain this query, i am confused.
Mar 3 '07 #9

100+
P: 153
I dont beleive you can use rownum by itself. the reason is that when rows are deleted from a table newly inserted rows are put in its place, so if you query by rownum the newly inserted row may not be in the last 10 rows of the table (which is all rownum is without some kind of order by in the query).

even a date_modified column may not work unless you just want some of the last 10 records modified even if 20 records were modified at the same time. if that's the case then you could select all the rows from the table ordered by the modified date desc and then grab the first 10 rows using your rownum psuedo column.
Mar 3 '07 #10

P: 2
[hi all:

how can i get he third largest salary from a table that contain salaries of all employees.
Mar 5 '07 #11

100+
P: 153
[hi all:

how can i get he third largest salary from a table that contain salaries of all employees.

This will get it for you, it uses an analytic function that gives a row a number based upon values provided in the window clause. you should learn about analytics... there are very useful at times.

Expand|Select|Wrap|Line Numbers
  1. SELECT a.salary
  2. FROM   (SELECT   salary,
  3.                  ROW_NUMBER() OVER(ORDER BY salary DESC) my_row_number
  4.         FROM     my_salary_table
  5.         ORDER BY salary DESC) a
  6. WHERE  my_row_number = 3
  7.  
Mar 7 '07 #12

P: 1
How to get the last modified row? only one that is lastly modified....
Nov 17 '07 #13

P: 1
in sql server I use profiler and works fine for all such needs
Feb 23 '10 #14

OraMaster
100+
P: 135
Is there any date (like add_date or M mod_date) type column you have in table.

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2.   FROM (SELECT ROWNUM seq, lat_rec.*
  3.           FROM (SELECT   <tablename>.*
  4.                     FROM <tablename>
  5.                 ORDER BY GREATEST (admitdt, moddt) DESC) lat_rec)
  6.  WHERE seq < 11
Hope this will help you.
Feb 25 '10 #15

P: 3
I think vijaydiwakar is right. b'coz oracle creates the rownum when a record is inserted into a table but not at the time of update in the record so it not possible to select the last updated records using the rownum, rather u have to add one more column say Updated which will contain the date on which the record is lastly updated.
Mar 17 '10 #16

OraMaster
100+
P: 135
DipakYadav,
Oracle doesn't create ROWNUM at the time of insertion. It's ROWID which gets created for each row when insertion happens. Oracle assigns unique number to each record at the time of select/update in the order it fetches the record from tables.
Mar 17 '10 #17

P: 1
@rathinavelpec
u cant really say,,when your last modify is used...

1.u may use modify command...
2.u may use alter command...
3.u may use update command....

so the records can be anywhere with in the table....
also you may modify or delete or alter or update before,hour,day,month,year,, we dont have perfection in oracle sql..

but we can get the last row inserted....which is utmost last

select * from emp where rowid=(select max(rowid) from emp);
Dec 5 '11 #18

P: 1
select * from emp where 2=(select count(*) from emp e where emp.sal<e.sal);

its also return the 3 hight sal
3 Days Ago #19

Post your reply

Sign in to post your reply or Sign up for a free account.