Hi all -
I have a scenerio i need assistance with. My scenerio is as follows:
I am building a snapsot table that assigns a batchid everytime it is run. These Id's are NOT necessarily in sequential order, but are always of a numeric type (BIGINT). -- Example: 900,001 then 900,020 then 900,101 etc....
I want to write a delete statement that looks at these and deletes only the oldest, keeping the last 5. With them not being in sequential oreder, what is a good way to do that?
Thanks for everyones input. Please let me know if there is any further clarification needed :)
In your examples the value of id always increases.
If this is the case something like this will work to identify the ones you want to delete.
Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 3936 StartFragment: 314 EndFragment: 3904 StartSelection: 314 EndSelection: 314
CREATE VOLATILE TABLE top5
( Adt_id BIGINT) PRIMARY INDEX (adt_id) ON COMMIT PRESERVE ROWS;
INSERT INTO top5
SEL TOP 5 adt_id FROM Youtable ORDER BY adt_id DESC;
SEL Yourtable.adt_id WHERE adt_id NOT IN (SEL adt_id FROM top5);
if duplicate ID is allowed then you can use RANDOM() function with BIGINT range.
CREATE VOLATILE TABLE tbl_name
( Adt_id BIGINT,time_stamp timestamp(6)) PRIMARY INDEX (adt_id) ON COMMIT PRESERVE ROWS;
if you add timestamp column also then you can sort and delete result based on that column
INSERT INTO tbl_name
SEL random(1, 9223372), current_timestamp
now you can delete the records
delete from tbl_name where time_stamp<date-5