How to fetch only the latest ordered items from the list by eliminating duplicate items SQL

Question

How to write SQL query for fetch only the latest ordered items and remove the duplicated items. here BOOK Ordered on 2 days so fetch only latest ordered recorded.

expected result

so here two rows removed and pick the latest ordered items . PEN and BOOK ordered two times but only took the latest order.

Answer 1

Here is full working example:

CREATE TABLE example_table (
   ID NUMBER PRIMARY KEY,
   ITEM_ID NUMBER,
   ORDER_ID NUMBER,
   NAME VARCHAR2(50),
   "Date" DATE
);

INSERT INTO example_table (ID, ITEM_ID, ORDER_ID, NAME, "Date") VALUES (101, 205, 301, 'CAP',  to_date('12-12-2022','dd-mm-yyyy'));
INSERT INTO example_table (ID, ITEM_ID, ORDER_ID, NAME, "Date") VALUES (102, 201, 303, 'BOOK', to_date('01-01-2023','dd-mm-yyyy'));
INSERT INTO example_table (ID, ITEM_ID, ORDER_ID, NAME, "Date") VALUES (103, 202, 303, 'PEN',  to_date('01-01-2023','dd-mm-yyyy'));
INSERT INTO example_table (ID, ITEM_ID, ORDER_ID, NAME, "Date") VALUES (104, 201, 304, 'BOOK', to_date('01-05-2023','dd-mm-yyyy'));
INSERT INTO example_table (ID, ITEM_ID, ORDER_ID, NAME, "Date") VALUES (105, 205, 304, 'BAG',  to_date('01-05-2023','dd-mm-yyyy'));
INSERT INTO example_table (ID, ITEM_ID, ORDER_ID, NAME, "Date") VALUES (106, 202, 305, 'PEN',  to_date('01-07-2023','dd-mm-yyyy'));

SELECT ID, ITEM_ID, ORDER_ID, NAME, "Date"
FROM
(
  SELECT ID, ITEM_ID, ORDER_ID, NAME, "Date"
        ,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY "Date" DESC) AS "RowID"
  FROM example_table
) DS
WHERE "RowID" = 1
ORDER BY ID

Answer 2

Try this

select * 
from( select *, ROW_NUMBER() OVER(PARTITION BY ITEM_ID ORDER BY DATE DESC) as rn
      from have 
    ) as a
where rn = 1
;