This is a simplified version of my problem but it's basically the same as what I have typed below...
I have a view which displays products & orders from my DB.
It selects from the products table and 'left outer joins' the product to the order detail table which in turn is 'inner joined' to the orders table. A pretty standard setup....
To return my records into a VB recordset I use the following statement:
select * from MY_VIEW where order_date >= start_date_var and order_date <= end_date_var
this displays all the orders within a date range fine...
Ok, now this is where I get a spot of bother. Within any results I select there may be a few instances of the same product. I basically want to filter the records I get returned to only display ONE instance of each product, basically showing only the latest sales order.
Does any one know how I can do this??
I have messed with the max/group by functions but I still get several instances of the same product....
Thanks in advance for any help you can offer.