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.
create multiset volatile table vt_max_date as
select vendorID,max(CheckDate) as maxDate
group by vendorID
on commit preserve rows;
from vendor_info_table a, vt_max_date b
where a.vendorID = b.vendorID
and a.checkDate =b.maxDate;
Hope this helps..