Oracle SQL - rownum based on column values

Question

I have the following table:

SPARE_PARTS Columns:

ID      LOC     ITEM        TRAN    CREATE_DATE
105534  12005   00123890    79047   04-JUN-21 03.19.26.982000000 PM
105535  12005   00123890    79047   04-JUN-21 03.40.26.982000000 PM
139500  16003   00173892    79047   09-JUL-21 11.14.00.749000000 PM
139501  16003   00173892    79047   09-JUL-21 11.18.00.749000000 PM

I need to the result as:

105535  12005   00123890    79047   04-JUN-21 03.40.26.982000000 PM
139501  16003   00173892    79047   09-JUL-21 11.18.00.749000000 PM

I need the records of distinct items with the maximum create date having the same Tran

If I use the rownum function, it will only bring one item.

Answer 1

Use PARTITION BY:

WITH xrows AS (
    SELECT t.*
         , ROW_NUMBER() OVER (PARTITION BY item ORDER BY create_date DESC) AS n
      FROM spare_parts t
     )
SELECT *
  FROM xrows
 WHERE n = 1
;