oracle sql

smalig
smalig used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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 EghtebasDatabase and Application Developer

Commented:
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

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I don't believe that syntax works in Oracle...
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
If you really want to delete the rows, something like:

delete from some_table where rowid in (
select row_to_delete from(
 select rowid row_to_delete, row_number() over(partition by column1 order by column2 desc) rn
 from some_table
 )
 where rn>1
)
/

Author

Commented:
thanks a lot slightwv. it worked

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial