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,sa l 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.