My table looks like this:
ID FROM WHEN
1 mario 24.10.19
1 robin 23.10.19
2 mario 24.10.19
3 robin 23.10.19
3 mario 22.10.19
I just want the newest records from an ID. So the result should look like this:
ID FROM WHEN
1 mario 24.10.19
2 mario 24.10.19
3 robin 23.10.19
I dont know how to get this result
There are multiple methods. For just three columns in Oracle, I have had good luck with group by
:
select id,
max("from") keep (dense_rank first order by "when" desc) as "from",
max("when") as when
from t
group by id;
Often a correlated subquery performs well, in this case, with an index on (id, when)
:
select t.*
from t
where t."when" = (select max(t2."when") from t t2 where t2.id = t.id);
And the canonical solution is to use window functions:
select t.*
from (select t.*,
row_number() over (partition by id order by "when" desc) as seqnum
from t
) t
where seqnum = 1;
Oracle has a smart optimizer but this has to do a bit more work, because row numbers are assigned to all rows before the filtering. That can make this a wee bit slower (in some databases) than alternative, but it is still a very viable solution.