List the most up-to-date product of each category,postqresql queries

Question

user_id product_id  category_id date_added  date_update
1              2        1           2.3.2021    null
1              3        1           2.3.2020    2.4.2023
1              4        2           2.3.2020    null
1              5        2           2.3.2020    2.4.2023
2              5        2           2.3.2020    2.4.2023
2              4        1           2.3.2020    null

List the most up-to-date product of each category

Answer 1

You can use row_number()

select * from
(
select *,row_number() over(parition by userid,category_id order by date_update) as rn
from tablename
)A where rn=1

OR you can also use distinct on

select distinct on (user_id,category_id) *
FROM tablename
ORDER BY user_id,category_id, date_update