Oracle SQL - Delete subset of records based on criteria

Question

I have the following table

ITEM    LOCATION    EFFECTIVE_DATE  SELLING_RETAIL
00139259    11001   01-JUL-20           2
00139259    11001   08-OCT-19           1
00139259    11001   04-OCT-19           1
00139259    11001   01-JAN-18           2
00139259    11001   04-DEC-17           1
00139259    11001   02-MAR-17           500

I need to keep only three records for item, location and effective date based on the descending order.

so that I remain with:

ITEM    LOCATION    EFFECTIVE_DATE  SELLING_RETAIL
00139259    11001   01-JUL-20           2
00139259    11001   08-OCT-19           1
00139259    11001   04-OCT-19           1

and all the rest should be delete.

there are many item and location in the table, I need to keep 3 records for the combination of item/location

Answer 1

You want to use the ROW_NUMBER analytic function to find the rows after the latest three and then correlate with the DELETE statement using the ROWID pseudo-column:

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
)

Which, for your sample data:

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;

Then after the deletion:

SELECT * FROM table_name;

Outputs:

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

db<>fiddle here