I am building a query to show VendorID, Check#, and CheckDate
Vendors have many check numbers and many check dates, I just want to pull the most recent check number and date per given vendor.
Help?
create multiset volatile table vt_max_date as
(
select vendorID,max(CheckDate) as maxDate
from vendor_info_table
group by vendorID
)
with data
on commit preserve rows;
select a.vendorID,a.checkNum
from vendor_info_table a, vt_max_date b
where a.vendorID = b.vendorID
and a.checkDate =b.maxDate;
Hope this helps..
Mandeep