I have an Oracle table which has a name,value,time columns.Basically the table is for logging purposes to store what are the changes made to a particular name,what was the previous value and what time the change was made.
I need to formulate a query to fetch the top n changes for a particular name,and the output should have all the names in the table. Any help/suggesstions?
Edit:
Name Value Time Harish Pass 1-Nov-2011 Ravi Fail 2-Nov-2011 Harish Absent 31-Oct-2011 Harish Attended 31-Aug-2011 Harish Present 31-Jul-2011
I need to select details of Harish on 1st Nov,Oct 31st,31st Aug and Ravi.
Is this what you are after?
My test set-up:
SQL> alter session set nls_date_format = 'DD-Mon-YYYY HH24:Mi:SS';
Session altered.
SQL> drop table so_test;
Table dropped.
SQL> create table so_test (
2 n varchar2(32)
3 , v varchar2(32)
4 , t date );
Table created.
SQL>
SQL> insert into so_test values ( 'X' , 'Test1', to_date('01-Jan-2011 12:00:00','DD-Mon-YYYY HH24:Mi:SS') );
1 row created.
SQL> insert into so_test values ( 'X' , 'Test2', to_date('01-Jan-2011 13:00:00','DD-Mon-YYYY HH24:Mi:SS') );
1 row created.
SQL> insert into so_test values ( 'X' , 'Test3', to_date('01-Jan-2011 14:00:00','DD-Mon-YYYY HH24:Mi:SS') );
1 row created.
SQL> insert into so_test values ( 'Y' , 'Test5', to_date('02-Jan-2011 12:00:00','DD-Mon-YYYY HH24:Mi:SS') );
1 row created.
SQL> insert into so_test values ( 'Y' , 'Test6', to_date('03-Jan-2011 12:00:00','DD-Mon-YYYY HH24:Mi:SS') );
1 row created.
SQL> insert into so_test values ( 'Y' , 'Test7', to_date('04-Jan-2011 12:00:00','DD-Mon-YYYY HH24:Mi:SS') );
1 row created.
SQL>
Here is the query:
SQL> select n,v,t from (
2 select n, v , t , rank() over ( partition by n order by t desc) r
3 from so_test
4 ) where r <= 2;
N V T
-------------------------------- -------------------------------- --------------------
X Test3 01-Jan-2011 14:00:00
X Test2 01-Jan-2011 13:00:00
Y Test7 04-Jan-2011 12:00:00
Y Test6 03-Jan-2011 12:00:00
SQL>