Home » SQL & PL/SQL » SQL & PL/SQL » analytic function (10.1.0.4.0)
icon1.gif  analytic function [message #415314] Mon, 27 July 2009 04:21 Go to next message
cherry
Messages: 56
Registered: December 2007
Member
hi all
im posting only a relevant portion of my prob.
i know its simple, but i need pointers or help to get this through.
create table test2 (creation_date date);

insert into test2 values (to_date('17-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('17-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('20-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('20-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('20-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('21-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('21-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('21-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('21-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('22-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('22-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('23-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('23-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('23-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('24-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('24-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('27-jul-2009', 'dd-mon-yyyy'));
insert into test2 values (to_date('27-jul-2009', 'dd-mon-yyyy'));

select * from test2;

CREATION_DATE
-------------
17/07/2009
17/07/2009
20/07/2009
20/07/2009
20/07/2009
21/07/2009
22/07/2009
22/07/2009
21/07/2009
21/07/2009
21/07/2009
23/07/2009
23/07/2009
23/07/2009
24/07/2009
24/07/2009
27/07/2009
27/07/2009

at any point of time, i should have latest 2 days old data.

how do i delete data NOT IN ('27-JUL-2009', '24-JUL-2009')?

in this case the 3rd latest date is '23-JUL-2009'. but the 3rd latest date can be any date, even a month / year old date.

after delete the data should be ..
CREATION_DATE
-------------
24/07/2009
24/07/2009
27/07/2009
27/07/2009


thanks

Report message to a moderator

Re: analytic function [message #415318 is a reply to message #415314] Mon, 27 July 2009 04:35 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Use RANK (or DENSE_RANK, I keep forgetting this) to determine which are not the two most recent dates.

Report message to a moderator

Re: analytic function [message #415319 is a reply to message #415314] Mon, 27 July 2009 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68723
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Have a look at DENSE_RANK function.

Regards
Michel

Report message to a moderator

Re: analytic function [message #415500 is a reply to message #415318] Tue, 28 July 2009 01:55 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
im new to analytics. this is what i've got.
SELECT creation_date,
       DENSE_RANK () OVER (PARTITION BY NULL ORDER BY creation_date)
  FROM test2

CREATION_DATE RANK
------------- ----
17/07/2009    1
17/07/2009    1
20/07/2009    2
20/07/2009    2
20/07/2009    2
21/07/2009    3
21/07/2009    3
21/07/2009    3
21/07/2009    3
22/07/2009    4

if the first step above is correct, how to form the delete?

thanks

Report message to a moderator

Re: analytic function [message #415501 is a reply to message #415500] Tue, 28 July 2009 01:56 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
C'mon.. at least show us something you tried.
You see the numbers, you know what to delete.

Report message to a moderator

Re: analytic function [message #415509 is a reply to message #415500] Tue, 28 July 2009 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68723
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
As you want to keep the last 2 dates, it is easier if you rank them in the reverse order.

Regards
Michel

Report message to a moderator

Re: analytic function [message #415513 is a reply to message #415509] Tue, 28 July 2009 02:19 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
thanks Michel.
but im not able to form the delete with analytic.
i know i have to delete rows less than what i get from the below select.
SELECT MAX (RANK) - 1
  FROM (SELECT   creation_date,
                 DENSE_RANK () OVER (PARTITION BY NULL ORDER BY creation_date)
                                                                         RANK
            FROM test2
        ORDER BY RANK DESC)

[Updated on: Tue, 28 July 2009 02:31]

Report message to a moderator

Re: analytic function [message #415520 is a reply to message #415513] Tue, 28 July 2009 02:59 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
You may want to take a look at the follwing query.

delete from test2 where rowid in (
 select rn from (SELECT  rowid rn, creation_date,
       DENSE_RANK () OVER (PARTITION BY NULL ORDER BY creation_date desc) cnt
  FROM test2) where cnt > 2)


I obtained it from

http://www.orafaq.com/faq/how_does_one_eliminate_duplicates_rows_from_a_table

Michel/Frank may have a better approach in handling this

Report message to a moderator

Re: analytic function [message #415522 is a reply to message #415520] Tue, 28 July 2009 03:03 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Without use of analytic functions:
delete from test2
  where creation_date < (select min(creation_date) 
                         from (select distinct creation_date
                               from test2
                               order by creation_date desc
                              ) 
                         where rownum < 3
                        );

Report message to a moderator

Re: analytic function [message #415555 is a reply to message #415314] Tue, 28 July 2009 04:12 Go to previous messageGo to next message
alammas
Messages: 46
Registered: July 2008
Member
Check it.

 delete from test2 
     where creation_date not in
	 (SELECT creation_date FROM 
		( SELECT creation_date , RANK() OVER (ORDER BY creation_date  DESC ) date_rank
            	 FROM test2 group by creation_date)
          WHERE date_rank <= 2 )
 /

Report message to a moderator

Re: analytic function [message #415715 is a reply to message #415555] Wed, 29 July 2009 00:10 Go to previous message
cherry
Messages: 56
Registered: December 2007
Member
Thank you alammas, Littlefoot & ajitpal.s for your help.

Report message to a moderator

Previous Topic: grant execute to procedure
Next Topic: how can we create procedure for all users to connect all user tables?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Dec 13 06:31:12 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.