Retriving Last ten modified Records

[Rks]

Hi All,

How can i select last ten modified records in table.

Can u please help me.

[vijaydiwakar]

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

[zafarkarachi]

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

[zafarkarachi]

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

[vijaydiwakar]

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

[rectoverso]

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

[zafarkarachi]

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

[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

[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.

[Dave44]

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.

[nalinikate]

[hi all:

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

[Dave44]

[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.

SELECT a.salary
FROM   (SELECT   salary,
                 ROW_NUMBER() OVER(ORDER BY salary DESC) my_row_number
        FROM     my_salary_table
        ORDER BY salary DESC) a
WHERE  my_row_number = 3
 

[rathinavelpec]

How to get the last modified row? only one that is lastly modified....

[aMahmoody]

in sql server I use profiler and works fine for all such needs

[OraMaster]

Is there any date (like add_date or M mod_date) type column you have in table.

SELECT *
  FROM (SELECT ROWNUM seq, lat_rec.*
          FROM (SELECT   <tablename>.*
                    FROM <tablename>
                ORDER BY GREATEST (admitdt, moddt) DESC) lat_rec)
 WHERE seq < 11

Hope this will help you.

[dipakyadav]

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.

[OraMaster]

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.

[KARTHIKcool]

@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);

[mahesh1193]

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

its also return the 3 hight sal