Hi All,
I am trying to find a latest record based on date or time for a particular set of records. I am using first few rows fetch function in Oracle 12c but it is showing only the very latest record .
Here is my query :
Table:
create table id_det(id number );
create table det_add (id number , id_date date,id_add varchar2(50));
inserting rows :
insert into id_Det values (1);
insert into id_Det values (2);
insert into id_Det values (3);
----------------
insert into det_add values (1, '29-Aug-21','S Street');
insert into det_add values (1, '19-Aug-21','M Street');
insert into det_add values (1, '22-Aug-21','T Street');
insert into det_add values (2, '04-Aug-21','S Street');
insert into det_add values (2, '23-Aug-21','M Street');
insert into det_add values (2, '08-Aug-21','T Street');
insert into det_add values (3, '7-Aug-21','S Street');
insert into det_add values (3, '11-Aug-21','M Street');
insert into det_add values (3, '04-Aug-21','T Street');
Query:
SELECT b.id_add FROM id_Det a, det_add b where a.id= b.id
ORDER BY a.id, b.id_date desc fetch first 1 rows only;
Output :
id id_date id_add
1 29-Aug-21 S street
But i need fetch latest record for id 1,2 and 3 . I used row_number or rank function but it is showing rank as 1 for records if dates are same while 'fetch first 1 rows only' gets the latest record . So i am not using rank function. Could anyone please advise me ,
Regards,
Uday