I'm working on some extra credit for a class by trying to provide 2 solutions for a problem.
I have the first solution working fine using rank() but I can't seem to figure out the second solution.
Can someone please help me out.
/* works fine */
with test (id, y, datum) as
(select 'a', 1, 2020 from dual union all
select 'a', 2, 2021 from dual union all
select 'a', 2, 2022 from dual union all
select 'b', 1, 1999 from dual union all
select 'b', 1, 2015 from dual union all
select 'c', 3, 2001 from dual union all
select 'c', 3, 2004 from dual union all
select 'c', 7, 2010 from dual
),
temp as
(select id, y, datum,
rank() over (partition by id order by datum desc) rnk
from test
)
select id, y, datum
from temp
where rnk = 1;
ID Y DATUM
-- ---------- ----------
a 2 2022
b 1 2015
c 7 2010
/* problem here */
with test (id, y, datum) as
(select 'a', 1, 2020 from dual union all
select 'a', 2, 2021 from dual union all
select 'a', 2, 2022 from dual union all
select 'b', 1, 1999 from dual union all
select 'b', 1, 2015 from dual union all
select 'c', 3, 2001 from dual union all
select 'c', 3, 2004 from dual union all
select 'c', 7, 2010 from dual
),
temp as SELECT t.ID
, t.y
, t.datum
FROM temp t
,( SELECT ID
, MAX(datum) dt
FROMt temp
GROUP BY ID ) t2
WHERE t.id = t2.id
AND t.datum = t2.datumSe