CODEWITHSUNDEEP
sqloracle-database
A2N15
A2N15

Reputation: 605

Retrieve latest date oracle

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!

Upvotes: 1

Views: 59

Answers (3)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

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;

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You seem to want:

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

Upvotes: -1

Related Questions