Oracle Sql - How Do I Get the Most Updated Value?
I'm Trying to Pull in the Most Recently Updated Lines. I Tried Using the Max Function in the Select Statement but Am Just Getting the Same Results as I Do When...
I'm trying to pull in the most recently updated lines. I tried using the MAX function in the select statement but am just getting the same results as I do when just doing a select *:
select max(last_update_date),vendor_id, last_update_date, last_updated_by , VENDOR_SITE_CODE
from POZ_SUPPLIER_SITES_ALL_M
group by vendor_id, last_update_date, last_updated_by, VENDOR_SITE_CODE
This is the table:
This is the result I want:
What condition or function do I have to use to get the results I want?
2 Answers
You can use row_number to achieve your expected result.
select
vendor,
vendor_site
last_update_date,
last_updated_by
from
(
select
vendor,
vendor_site
last_update_date,
last_updated_by,
row_number() over (partition by vendor, vendor_site order by last_update_date desc) as rnk
from POZ_SUPPLIER_SITES_ALL_M
) val
where rnk = 1
One method uses a correlated subquery:
select t.*
from t
where t.lastupdateddate = (select max(t2.lastupdateddate)
from t t2
where t2.vendor = t.vendor and t2.vendorsite = t.vendorsite
);
Another method uses aggregation and keep:
select vendor, vendorsite, max(lastupdateddate),
max(lastupdatedby) keep (dense_rank first order by lastupdateddate desc) as lastupdatedby
from t
group by vendor, vendorsite;
The keep expression is Oracle's (rather verbose) way of saying "first".
Of course, there is also the row_number() approach as well.
Sophia analyzes international trade, startup ecosystems, retail transformation, and supply chain logistics for modern digital publications.
