Oracle:Get Most recent Transaction based on updated Date ad like column

Question

Hi I am trying to get most recent transaction based on updated timestamp along with another column like i i tried with below queries but did not work.

Create Table Transactions(Transaction_Id number,User_ID varchar(20),Transaction_Name varchar(20),Upd_Dt Date);

Insert into Transactions(Transaction_Id,User_ID,Transaction_Name,Upd_Dt)values(123,'User1','FROM_CUSTOME_ON_0107',to_date('11/26/2013','mm/dd/yyyy'));
Insert into Transactions(Transaction_Id,User_ID,Transaction_Name,Upd_Dt)values(124,'User1','FROM_CUSTOME_ON_0207',to_date('12/26/2013','mm/dd/yyyy'));
Insert into Transactions(Transaction_Id,User_ID,Transaction_Name,Upd_Dt)values(125,'User1','FROM_CUSTOME_ON_0207',to_date('01/26/2013','mm/dd/yyyy'));

Insert into Transactions(Transaction_Id,User_ID,Transaction_Name,Upd_Dt)values(126,'User1','FROM_CUSTOME_ON',to_date('02/26/2013','mm/dd/yyyy'));
Insert into Transactions(Transaction_Id,User_ID,Transaction_Name,Upd_Dt)values(127,'User1','FROM_CUSTOME_ON',to_date('03/26/2013','mm/dd/yyyy'));
Insert into Transactions(Transaction_Id,User_ID,Transaction_Name,Upd_Dt)values(128,'User1','FROM_CUSTOME_ON',to_date('04/26/2013','mm/dd/yyyy'));

Tried Query:

Select tl1.Transaction_Id from Transactions tl1 
where tl1.Upd_Dt = (Select max(tl2.Upd_Dt) from Transactions tl2) and tl1.Transaction_Name 
like 'FROM_CUSTOME_ON_%';

How to get exact record which LIKE 'FROM_CUSTOME_ON_MMYY'?

all `Upd_Dt` values are equal, and they don't have any time portions, can you fix the sample data? — Barbaros Özhan, Dec 20 '20 at 20:22

Barbaros Özhan · Answer 1 · 2020-12-20T20:33:11.843

You need DENSE_RANK() analytic function, by which all tied records for the values of the Upd_Dt column are fetched if exists more than one

Use one of the queries below

for Oracle11g :

SELECT Transaction_Id
  FROM
  (
   SELECT Transaction_Id, DENSE_RANK() OVER (ORDER BY Upd_Dt DESC) AS dr
     FROM Transactions tl1
    WHERE Transaction_Name like 'FROM_CUSTOME_ON_%'
   )
 WHERE dr = 1

for Oracle12c :

SELECT Transaction_Id
  FROM Transactions tl1
 WHERE Transaction_Name like 'FROM_CUSTOME_ON_%'
 ORDER BY DENSE_RANK() OVER (ORDER BY Upd_Dt DESC) 
 FETCH NEXT 1 ROW WITH TIES

but, in the current case all date values equal, and all records return as the recent, that seems having no sense. At least, you can add the time portions for the date values, and it seems you mean this by mentioning about timestamp.

Oracle:Get Most recent Transaction based on updated Date ad like column

1 Answers1