Translated to capabilities of VFP SQL:
Code:
Select * From Product_Prices;
Inner join (select NAME, max(PDATE) as max_pdate;
from Product_Prices;
group by NAME) as Latest_Prices;
On Product_Prices.Name = latest_prices.Name AND Product_Prices.Pdate = Latest_Prices.max_pdate
To avoid some problems and have a better performance, it is advisable to not store the data you have in this way. There should be a products table that has an id per productname, then a vendors table with id and vendor name. When you group by an id, no matter if it is a 4 byte integer or a longer guid, that's faster done than grouping by a long char field like the name is. besides, it's in general always advisable to store your data in a normalized database design.
our product_prices table then has a productid instead of productname and a vendorid instead of vendorname. For (humanreadable) display you always can join in the names and show them instead of ids, but ids are better performing and also don't have problems with comparisons in different collations and codepages chracter fields could have. It simple takes less times to group by data in less bytes, to make the explanation short.
Then this becomes your query:
Code:
Select ;
Products.name as Product_Name,;
Product_Prices.Price,;
Product_Prices.Price_Date,;
Vendors.Name as Vendor_Name;
From Product_Prices;
Inner join (select product_id, vendor_id, max(price_date) as Max_price_date;
from Product_Prices;
group by product_id, vendor_id) as Latest_Prices;
On Product_Prices.product_id = latest_prices.product_id ;
AND Product_Prices.vendor_id = latest_prices.vendor_id ;
AND Product_Prices.price_date = Latest_Prices.Max_price_date;
Left Join Products on Product_Prices.Product_id = Products.Id;
Left Join Vendors On Product_Prices.Vendor_id = Vendors.Id
Or, if you're only interested in the latest price and remove all records of other vendors with an older price, then don't group by vendors, only group by products:
Code:
Select ;
Products.name as Product_Name,;
Product_Prices.Price,;
Product_Prices.Price_Date,;
Vendors.Name as Vendor_Name;
From Product_Prices;
Inner join (select product_id, max(price_date) as Max_price_date;
from Product_Prices;
group by product_id) as Latest_Prices;
On Product_Prices.product_id = latest_prices.product_id ;
AND Product_Prices.price_date = Latest_Prices.Max_price_date;
Left Join Products on Product_Prices.Product_id = Products.Id;
Left Join Vendors On Product_Prices.Vendor_id = Vendors.Id
Another idea would be to store the latest price you know of a product per vendor in a table current_vendor_prices. And store historical prices separately in an archive table product_price_history. Both tables have the same structure product_id, vendor_id, price and price_date. And whenever you have a newer price for a product and vendor you take the record you have in current_vendor_prices and add it to product_price_history, then update the price and date in the current_vendor_prices table and use that table instead of a query.
So just using a current_vendor_prices will show you the result as per the first query without actually needing to query, you just eed to join in the product and vendornames, but not need to group by anything, as you only have latest prices records anyway. And if you then only want the latest prices independent from the vendor, you can still use a group by query again:
Code:
Select ;
Products.name as Product_Name,;
Product_Prices.Price,;
Product_Prices.Price_Date,;
Vendors.Name as Vendor_Name;
From Current_Product_Prices;
Inner join (Select Product_id, MAX(price_date) as Max_price_date;
From Current_Product_Prices;
Group By Product_Id) as Latest_Prices;
On Product_Prices.Product_id = Latest_Prices.Product_id ;
And Product_Prices.Price_date = Latest_Prices.Max_price_date;
Left Join Products on Product_Prices.Product_id = Products.Id;
Left Join Vendors On Product_Prices.Vendor_id = Vendors.Id
And again, if you're always only interested in this list independent from vendors, you can only store those records in Current_Product_Prices and whenever you have a new price input, no matter from which vendor, you store the current record in the history table and update, price, price date and vendor_id in the current price record.
It's a matter of preferences and taste how exactly you structure your data, but at a minimum keep separate tables for the separate lists, like vendors and products, and in tables combining data use IDs as references to these basic data lists. Without deep-diving into database design theory you can easily think of these separate lists that make sense and which become providers of names of vendors, products, etc. for human readable display on the one side and simpler, faster, not error-prone processing by integer IDs or guids on the other side.
Chriss
