Get last value from a certain group (Oracle)

I have something like this

Date Group ID
11/01 'A' 1
12/01 'A' 2
13/01 'B' 3
14/01 'B' 4


What i basically want is to get for example the latest from group 'A'

Date Group ID LatestID_from_GROUP_A_ordered_by_recent_date
11/01 'A' 1 2
12/01 'A' 2 2
13/01 'B' 3 2
14/01 'B' 4 2


or at least something like this

Date Group ID LatestID_from_GROUP_A_ordered_by_recent_date
11/01 'A' 1 null
12/01 'A' 2 null
13/01 'B' 3 2
14/01 'B' 4 2


Where does Latest_from_A come from?
– user8035311
Jun 29 at 15:49

Sorry it's basically the Latest ID from group A which is 2 (it doesn't neccesarily the highest I want to be the latest date, since 12/01 is the latest basically)
– savemenico
Jun 29 at 15:51

@savemenico . . . Your data is unclear. Do you want the latest id overall or the latest as of the date in the row.
– Gordon Linoff
Jun 29 at 16:02

What happens if you add a row with 15/01 'A' 5 ?Do all the rows get '5' as the latest value, or do the first four still get '2'? (Which is basically what Gordon is asking...)
– Alex Poole
Jun 29 at 16:12

15/01 'A' 5

Please edit your question to show more examples, covering all the scenarios you can think of and what should be shown (and the logic involved)..
– Alex Poole
Jun 29 at 16:32

3 Answers
3

How about this:

with demo (somedate, somegroup, id) as
( select date '2018-01-11', 'A', 1 from dual union all
select date '2018-01-12', 'A', 2 from dual union all
select date '2018-01-13', 'B', 3 from dual union all
select date '2018-01-14', 'B', 4 from dual union all
select date '2018-01-15', 'A', 5 from dual -- example from comments
)
select somedate, somegroup, id
, ( select max(id) keep (dense_rank last order by somedate)
from demo
where somegroup = 'A' ) as last_a
from demo;

SOMEDATE SOMEGROUP ID LAST_A
----------- --------- ---------- ----------
11/01/2018 A 1 5
12/01/2018 A 2 5
13/01/2018 B 3 5
14/01/2018 B 4 5
15/01/2018 A 5 5


Note the max(id) is only a tiebreaker in the event of multiple rows with the last date.

max(id)

Gordon was almost there.

You want to create a window over your whole query, but only pick the biggest value of 'A':

select
t.*,
max(case when group = 'A' then id end) over (partition by 1) as latest_from_a
from t


'partition by 1' will create a window of your complete result set because it only groups by a single static value: 1.

The logic seems to be:

select t.*,
max(case when group = 'A' then id end) over (order by date) as latest_from_a
from t;


The above gets the cumulative maximum up to each date. If you want the overall maximum:

select t.*,
max(case when group = 'A' then id end) over () as latest_from_a
from t;


Actually no since they 1 and 2 might be flipped over but I still want the one with date 12/01
– savemenico
Jun 29 at 16:17

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.