Max date over each item

Question

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

score 2 · Answer 1 · edited Apr 14 '20 at 15:20

2

You can use row_number analytical function which will number each row according to partition by and order by clause:

Try this:

SELECT ITEM, COST, DAY FROM
(SELECT t.ITEM, t.COST, t.DAY,
ROW_NUMBER() over (partition by t.ITEM ORDER BY T.DAY DESC) AS RN
FROM TBL t)
WHERE RN = 1;

Demo

Cheers!!

edited Apr 14 '20 at 15:20

Boneist

22,910
1
25
40

answered Aug 21 '19 at 10:50

Popeye

35,427
4
10
31

That does seem correct even though I can't get it to work. Is it for Oracle 11g? I did tried to use OVER with PARTITION BY myself before asking but failed, also partition is mistyped in the code. – user7393973 Aug 21 '19 at 11:07
Sorry for typo mistake. And are you sure its not working. See the [demo](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=90b92afa3e42958b20b7945a198c5a1d) – Popeye Aug 21 '19 at 12:34
Nevermind it does work, just missing `()` after `ROW_NUMBER`. – user7393973 Aug 21 '19 at 12:40
Cool. It happens but glad to know that it worked :) – Popeye Aug 21 '19 at 12:45

score 2 · Accepted Answer · answered Aug 21 '19 at 10:59

2

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.

answered Aug 21 '19 at 10:59

Ponder Stibbons

14,723
2
21
24

It works. What does the `max(cost) keep (dense_rank last order by day)` exactly do? – user7393973 Aug 21 '19 at 11:01
2

It works like max, but criterium is not `cost`, but the day. So it finds value of cost for last day. In case there are two such values max() takes greater. If you want lower use min(). – Ponder Stibbons Aug 21 '19 at 11:04
Oh I see. I tried it out with an item having 2 different costs on the same date and could see that the max/min picks one cost while [forpas's answer](https://stackoverflow.com/a/57589945) picks both. Not too sure which answer to leave accepted since I don't think that happens but if it does I would probably only want 1 record to be returned so... – user7393973 Aug 21 '19 at 11:15
Though after trying to see if the other answer could also return one cost I found that it can by adding max/min to the cost and group by to the item and day. So both answers are correct and really good, can only accept one unfortunately but upvoted both. – user7393973 Aug 21 '19 at 11:34

score 1 · Answer 3 · answered Aug 21 '19 at 10:47

1

With NOT EXISTS:

SELECT t.ITEM, t.COST, t.DAY
FROM TBL t
WHERE NOT EXISTS (
  SELECT 1 FROM TBL
  WHERE ITEM = t.ITEM AND DAY > t.DAY
)

See the demo.
Results:

> ITEM | COST | DAY      
> :--- | ---: | :--------
> A    |    2 | 21-AUG-19
> B    |    4 | 21-AUG-19
> C    |    2 | 15-AUG-19

answered Aug 21 '19 at 10:47

forpas

160,666
10
38
76

That works though I'm confused since I never used NOT EXISTS. Can you explain the logic? – user7393973 Aug 21 '19 at 10:50
1

It returns only the rows for which there does not exists another row for the same item with greater date. Meaning the result consists only from the rows with the latest dates for each item. – forpas Aug 21 '19 at 10:52

Max date over each item

3 Answers3