clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805483 fiddles created (40775 in the last week).

CREATE TABLE TABLE_NAME ( ITEM, LOCATION, EFFECTIVE_DATE, SELLING_RETAIL ) AS SELECT '00139259', 11001, DATE '2020-07-01', 2 FROM DUAL UNION ALL SELECT '00139259', 11001, DATE '2019-10-08', 1 FROM DUAL UNION ALL SELECT '00139259', 11001, DATE '2019-10-04', 1 FROM DUAL UNION ALL SELECT '00139259', 11001, DATE '2018-01-01', 2 FROM DUAL UNION ALL SELECT '00139259', 11001, DATE '2017-12-04', 1 FROM DUAL UNION ALL SELECT '00139259', 11001, DATE '2017-03-02', 500 FROM DUAL;
6 rows affected
 hidden batch(es)


DELETE FROM table_name WHERE ROWID IN ( SELECT ROWID FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY item, location ORDER BY effective_date DESC ) AS rn FROM table_name ) WHERE rn > 3 )
3 rows affected
 hidden batch(es)


ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
 hidden batch(es)


SELECT * FROM table_name;
ITEM LOCATION EFFECTIVE_DATE SELLING_RETAIL
00139259 11001 2020-07-01 00:00:00 2
00139259 11001 2019-10-08 00:00:00 1
00139259 11001 2019-10-04 00:00:00 1
 hidden batch(es)