Home » SQL & PL/SQL » SQL & PL/SQL » delete last n sets of rows of a table with same dates in sort field
delete last n sets of rows of a table with same dates in sort field [message #579828] Fri, 15 March 2013 15:46 Go to next message
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?
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 Go to previous messageGo to next message
BlackSwan
Messages: 23159
Registered: January 2009
Senior Member
SELECT all the rows here
MINUS
SELECT 2 rows you want to exclude
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 Go to previous messageGo to next message
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);

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 Go to previous message
wbmoore2
Messages: 3
Registered: March 2013
Junior Member
I dont understand what you meant.
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:
  


Current Time: Sun Dec 21 22:12:40 CST 2014

Total time taken to generate the page: 0.14035 seconds