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.
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.
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
I don't believe that syntax works in Oracle...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks a lot slightwv. it worked
select column1, column2, row_number() over(partition by column1 order by column2 desc) rn
from some_table
)
where rn=1
/