Max date over each item

I have a table that simplified looks like this:

WITH TBL (ITEM, COST, DAY) AS (
  SELECT 'A', 6, TO_DATE('2019-08-13', 'YYYY-MM-DD') FROM DUAL
  UNION ALL
  SELECT 'B', 4, TO_DATE('2019-08-21', 'YYYY-MM-DD') FROM DUAL
  UNION ALL
  SELECT 'B', 4, TO_DATE('2019-08-18', 'YYYY-MM-DD') FROM DUAL
  UNION ALL
  SELECT 'A', 2, TO_DATE('2019-08-21', 'YYYY-MM-DD') FROM DUAL
  UNION ALL
  SELECT 'B', 5, TO_DATE('2019-08-16', 'YYYY-MM-DD') FROM DUAL
  UNION ALL
  SELECT 'C', 2, TO_DATE('2019-08-15', 'YYYY-MM-DD') FROM DUAL
) SELECT ITEM, COST, DAY FROM TBL;


ITEM | COST | DAY
-----+------+--------------------
A    | 6    | 2019-08-13 00:00:00
B    | 4    | 2019-08-21 00:00:00
B    | 4    | 2019-08-18 00:00:00
A    | 2    | 2019-08-21 00:00:00
B    | 5    | 2019-08-16 00:00:00
C    | 2    | 2019-08-15 00:00:00

I want to query the latest cost of each item.

ITEM | COST | DAY
-----+------+--------------------
B    | 4    | 2019-08-21 00:00:00
A    | 2    | 2019-08-21 00:00:00
C    | 2    | 2019-08-15 00:00:00

The way I do so is with a CTE getting the latest date of each item and then joining the cost.

WITH CTE (ITEM, DAY) AS (
  SELECT ITEM, MAX(DAY)
  FROM TBL
  GROUP BY ITEM
)
SELECT CTE.ITEM, TBL.COST, CTE.DAY
FROM CTE
JOIN TBL ON TBL.ITEM = CTE.ITEM AND TBL.DAY = CTE.DAY;

My question is if there’s a simpler way to do it in one query without the CTE.

I tried to combine the MAX with the GROUP BY but didn’t found any way to make it work.

SELECT ITEM, COST, MAX(DAY)
FROM TBL
GROUP BY ITEM, COST;

It only groups the records with the same item and cost when what I actually need is to group it accordingly with the MAX day.

ITEM | COST | DAY
-----+------+--------------------
A    | 6    | 2019-08-13 00:00:00
B    | 4    | 2019-08-21 00:00:00
A    | 2    | 2019-08-21 00:00:00
B    | 5    | 2019-08-16 00:00:00
C    | 2    | 2019-08-15 00:00:00

Answer

The simplest is min/max with first/last option:

select item, max(day), max(cost) keep (dense_rank last order by day) from tbl group by item;

^demo

Link to documentation and example.

Advertisement

Answer