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
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;
Link to documentation and example.