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.
