Select all items for each category but only for latest date per each category

Table schema is very simple item, category and date. I would like to query all items for each category but only for MAX date per each category.

Answer

I think this does what you want:

select t.*
from (select t.*,
             max(date) over (partition by category) as max_date
      from t
     ) t
where date = max_date;