Row_number not doing what i need (SQL)

I'm using ORACLE 11G database

I have a table which have these columns:

item, cod_line, business, cod_business, cod_ez_catalog_product_brand, business_unit, last_update_date

It is called: products_table

Take a look (i'm filtering for a specific product):

SELECT item, cod_line, business, cod_business, cod_ez_catalog_product_brand, business_unit, last_update_date
from products_table
WHERE ITEM = '94TERFERE'

I'm getting this output:

I want to get only one row within the most update "last_update_flag" value, in this case this one:

I did this:

SELECT item, cod_line, business, cod_business, cod_ez_catalog_product_brand, business_unit, last_update_date
from
(
SELECT 
item, cod_line, business, cod_business, cod_ez_catalog_product_brand, business_unit, last_update_date,
row_number() over (partition by last_update_date order by last_update_date desc) as rnum
FROM XX_OTC_PRODUCTOS_OUT 
where item in ('94TERFERE')
and end_date_active is null
order by last_update_date desc
)sub
where rnum = 1;

But i am not getting 1 row, i'm getting 8 rows from 10 records available!:

Desired result:

What am i doing wrong?

CodePudding user response：

You need to partition by item, not last_updated_date