Retrieve latest date oracle

Question

Below is a sample of the Table

id   from        to         value
1    01.01.20    19.01.20   100
1    20.01.20    31.12.99   100
2    01.01.20    19.01.20   1001
2    25.01.20    31.12.99   1001
3    01.01.20    19.01.20   1002
3    29.01.20    31.12.99   1002

The aim is to retrieve the latest row

The expected output should look like this

id   from        to         value
1    20.01.20    31.12.99   100
2    25.01.20    31.12.99   1001
3    29.01.20    31.12.99   1002

I tried to do something like this:

select *
from test
where id in a
AND from = (select max(from) 
   from test 
   where id in a);

However, I am retrieving only one row

3    29.01.20    31.12.99   1002

Thanks for anyone helping!

Please explain the logic. It is not clear. – Gordon Linoff May 19 '20 at 14:30 — Gordon Linoff, May 19 '20 at 14:30
This may help: https://stackoverflow.com/a/3491386/259889 – Sid May 19 '20 at 14:33 — Sid, May 19 '20 at 14:33

score 1 · Accepted Answer · answered May 19 '20 at 15:11

You need simple aggregation using MAX and GROUP BY:

with t as
(
    select 1 id, '01.01.20' "from", '19.01.20' "to", 100 "value" from dual union all
    select 1, '20.01.20', '31.12.99', 100 from dual union all
    select 2, '01.01.20', '19.01.20', 1001 from dual union all
    select 2, '25.01.20', '31.12.99', 1001 from dual union all
    select 3, '01.01.20', '19.01.20', 1002 from dual union all
    select 3, '29.01.20', '31.12.99', 1002 from dual
)
select id, max("from"), max("to"), "value" from t
group by id, "value"
order by id;

        ID from     to            value
---------- -------- -------- ----------
         1 20.01.20 31.12.99        100
         2 25.01.20 31.12.99       1001
         3 29.01.20 31.12.99       1002

On a side note, '01.01.20' is a string and not a date. So, please make sure you use appropriate DATE data type for date values.

score 1 · Answer 2 · answered May 19 '20 at 15:15

1

I think you need the latest row per ID -

SELECT 
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY id, from DESC) RN
      FROM test)
WHERE RN = 1;

answered May 19 '20 at 15:15

Ankit Bajpai

13,128
4
25
40

score -1 · Answer 3 · answered May 19 '20 at 14:31

-1

You seem to want:

select t.*
from test t
where t.to_date = '31.12.99'

answered May 19 '20 at 14:31

Gordon Linoff

1,242,037
58
646
786

That assumes that the max date will always be '31-12-99'. – Sid May 19 '20 at 14:32
This was a sample, the date isn't always 31.12.99 – A2N15 May 19 '20 at 14:33
@Si . . . I can only respond to the question that was asked. That is true of all the sample data in the question. – Gordon Linoff May 19 '20 at 14:47

Retrieve latest date oracle

3 Answers3