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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
select column1, column2 from(
select column1, column2, row_number() over(partition by column1 order by column2 desc) rn
from some_table
)
where rn=1
/
select column1, column2, row_number() over(partition by column1 order by column2 desc) rn
from some_table
)
where rn=1
/
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial