OraFAQ Forum: SQL & PL/SQL » delete last n sets of rows of a table with same dates in sort field

Home » SQL & PL/SQL » SQL & PL/SQL » delete last n sets of rows of a table with same dates in sort field

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

delete last n sets of rows of a table with same dates in sort field [message #579828]

Fri, 15 March 2013 15:46

wbmoore2
Messages: 3
Registered: March 2013

Junior Member

Hi. I know how to select the last N sets of rows, using DENSE_RANK - where multiple rows have the same timestamp but I want to only select those rows which do NOT have the top 2 unique timestamps.
i.e.:

SELECT *
FROM ( SELECT DENSE_RANK() OVER (ORDER BY myTimestamp DESC) DENSE_RANK, HISTORYID, USER_ID, myTimestamp, STATUS, FROM TXN_HIST)
WHERE DENSE_RANK > 2 order by myTimestamp DESC, HISTORYID, USER_ID;

But how do I DELETE these same rows?

Report message to a moderator

Re: delete last n sets of rows of a table with same dates in sort field [message #579915 is a reply to message #579828]

Sun, 17 March 2013 09:23

BlackSwan
Messages: 20109
Registered: January 2009

Senior Member

SELECT all the rows here
MINUS
SELECT 2 rows you want to exclude

Report message to a moderator

Re: delete last n sets of rows of a table with same dates in sort field [message #579974 is a reply to message #579915]

Mon, 18 March 2013 09:08

wbmoore2
Messages: 3
Registered: March 2013

Junior Member

I figured out the solution.

There was a slight typo in the original message. This is the correct select SQL:
SELECT *
FROM ( SELECT DENSE_RANK() OVER (ORDER BY myTimestamp DESC) DENSE_RANK, HISTORYID, USER_ID, myTimestamp, STATUS FROM TXN_HIST)
WHERE DENSE_RANK > 2 order by myTimestamp DESC, HISTORYID, USER_ID;

This SQL will delete all except the latest 2 sets records based upon 'myTimestamp':
DELETE TXN_HIST where rowid in (SELECT rowid
FROM ( SELECT DENSE_RANK() OVER (ORDER BY myTimestamp DESC) DENSE_RANK FROM TXN_HIST)
WHERE DENSE_RANK > 2);

For those who need to do this for a subset of the data, based upon other search field values:
this SQL will delete all except the latest 2 sets of records based upon 'myTimestamp' that are older than myTimestamp 28-SEP-12 for 'myUserid' and 'MY STATUS':
DELETE TXN_HIST where rowid in (SELECT rowid
FROM ( SELECT DENSE_RANK() OVER (ORDER BY myTimestamp DESC) DENSE_RANK FROM TXN_HIST where USER_ID = 'myUserid' and STATUS = 'MY STATUS' and myTimestamp < '28-SEP-12')
WHERE DENSE_RANK > 2);

Report message to a moderator

Re: delete last n sets of rows of a table with same dates in sort field [message #579977 is a reply to message #579915]

Mon, 18 March 2013 09:39

wbmoore2
Messages: 3
Registered: March 2013

Junior Member

I dont understand what you meant.

Report message to a moderator

Previous Topic:	substring and reference table
Next Topic:	populating the collection of date type with date values of all saturday 's of one month -- Help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 25 01:20:29 CDT 2013

Total time taken to generate the page: 0.06146 seconds