Home » SQL & PL/SQL » SQL & PL/SQL » Rows of a table
Rows of a table [message #560844] Tue, 17 July 2012 07:09 Go to next message
satheesh_ss
Messages: 54
Registered: July 2012
Member
write a query find which rows of a table is updated on 2
days before?

(OR)

In table there r so many rows write a query which two rows r updated last two day before?

[Updated on: Tue, 17 July 2012 07:17]

Report message to a moderator

Re: Rows of a table [message #560851 is a reply to message #560844] Tue, 17 July 2012 07:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1397
Registered: January 2010
Senior Member
Not possible, unless table has last_updated column which holds last update timestamp.

SY.

Report message to a moderator

Re: Rows of a table [message #560854 is a reply to message #560851] Tue, 17 July 2012 07:51 Go to previous messageGo to next message
Littlefoot
Messages: 16928
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How about this? 
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17.12.1980 00:00:00       1600                    20
      7499 ALLEN      SALESMAN        7698 20.02.1981 00:00:00       2400        300         30
      7521 WARD       SALESMAN        7698 22.02.1981 00:00:00       2050        500         30
      7566 JONES      MANAGER         7839 02.04.1981 00:00:00       3775                    20
      7654 MARTIN     SALESMAN        7698 28.09.1981 00:00:00       2050       1400         30
      7698 BLAKE      MANAGER         7839 01.05.1981 00:00:00       3650                    30
      7782 CLARK      MANAGER         7839 09.06.1981 00:00:00       3250                    10
      7788 SCOTT      ANALYST         7566 09.12.1982 00:00:00       3800                    20
      7839 KING       PRESIDENT            17.11.1981 00:00:00       5800                    10
      7844 TURNER     SALESMAN        7698 08.09.1981 00:00:00       2300                    30
      7876 ADAMS      CLERK           7788 12.01.1983 00:00:00       1900                    20
      7900 JAMES      CLERK           7698 03.12.1981 00:00:00       1750                    30
      7902 FORD       ANALYST         7566 03.12.1981 00:00:00       3800                    20
      7934 MILLER     CLERK           7782 23.12.1982 00:00:00       2100                    10

14 rows selected.

SQL> update emp set job = 'xxx' where ename = 'SMITH';

1 row updated.

SQL> select *
  2  from emp n,
  3      (select * from emp
  4       as of timestamp (sysdate - 2)
  5      ) b
  6  where n.job <> b.job
  7    and n.empno = b.empno;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      xxx             7902 17.12.1980 00:00:00       1600                    20
      7369 SMITH      CLERK           7902 17.12.1980 00:00:00       1600                    20


SQL>

Report message to a moderator

Re: Rows of a table [message #560860 is a reply to message #560854] Tue, 17 July 2012 08:02 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1397
Registered: January 2010
Senior Member
This would work (assuming undo retention period is 2+ days and there was enough undo to honor it) but wouldn't be 100% accurate.

SY.
P.S. you also need to consider column was updated from/to NULL.

Report message to a moderator

Re: Rows of a table [message #560861 is a reply to message #560844] Tue, 17 July 2012 08:04 Go to previous message
Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 17 July 2012 11:43
Michel Cadot wrote on Tue, 17 July 2012 11:36
From your previous topic:

Michel Cadot wrote on Tue, 17 July 2012 11:35
From your previous topic:

Michel Cadot wrote on Sat, 14 July 2012 16:11
None.

From your previous topics:

Michel Cadot wrote on Thu, 12 July 2012 19:52
...
You not only have to read the Concepts manual but also the Database SQL Reference.

Regards
Michel


Michel Cadot wrote on Thu, 12 July 2012 15:11
From your previous topic:

Michel Cadot wrote on Thu, 12 July 2012 14:08
Please feedback to your previous topics and thanks people that (try to) help you.
Also from your previous topics:

Michel Cadot wrote on Thu, 12 July 2012 08:39
All your questions are answered in Database Concepts.
Please read it.

Regards
Michel


Michel Cadot wrote on Thu, 12 July 2012 08:47
...
Database Concepts

Regards
Michel






Regards
Michel


In addition, this is a FAQ already posted a hundred time, so SEARCH before posting (accordingly to the guide).

Regards
Michel



What is your problem in writing this?
With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Use SQL*Plus and copy and paste what you already tried.

Regards
Michel


...

Report message to a moderator

Previous Topic: Oracle SQL Query
Next Topic: Exception Handling
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat May 18 20:40:09 CDT 2013

Total time taken to generate the page: 0.37633 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.