Avatar of smalig
smalig
 asked on

oracle sql

Hello experts,
I have a table with data, from which I need to delete records keeping only the latest. here is how the table looks like

column1                               column2 (timestamp column)

abc                                         2014/11/22 08:08:38
abc                                         2015/03/06 06:10:39
abc                                         2015/03/06 08:10:39
.
.
def                                         2015/03/06 06:10:39
def                                         2015/03/06 08:10:39
def                                         2015/03/06 10:10:39

I want to keep records which have the latest time stamp like

abc                                        2015/03/06 08:10:39
def                                         2015/03/06 10:10:39

deleting the rest of the data. how can I do this in a query.
Oracle DatabaseSQL

Avatar of undefined
Last Comment
smalig

8/22/2022 - Mon
slightwv (䄆 Netminder)

select column1, column2 from(
select column1, column2, row_number() over(partition by column1 order by column2 desc) rn
from some_table
)
where rn=1
/
Mike Eghtebas

Using the good solution from lightvw,

Delete from(
select column1, column2, row_number() over(partition by column1 order by column2 desc) rn
from some_table
)
where rn>1

Open in new window

slightwv (䄆 Netminder)

I don't believe that syntax works in Oracle...
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
smalig

ASKER
thanks a lot slightwv. it worked