CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Hailey
Hailey

Reputation: 69

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 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: enter image description here

This is the result I want:

enter image description here

What condition or function do I have to use to get the results I want?

Upvotes: 0

Views: 284

Answers (2)

zealous
zealous

Reputation: 7503

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270653

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.

Upvotes: 1

Related Questions