I have an SQL table "products" with fields like so:
id = index
pid = product id
cat = category
date = date product was updated
data1 = data pertaining to the product
I want to select data 1-5 for the latest update of each pid within a particular category. This table has multiple records of the same pid because a new record is added each time the product is updated, and the old ones are used for queries based on the history of the product. How would I write a select statement to do that?
OK so the idea is to retrieve the integer and the 4 strings for each of the unique products (pid) in a particular category if it has the newest time stamp.
So for example I want to narrow down the data to a sample set. Where cat ="1" in cat 1 there are 3 products (pid) 1000, 2000, and 3000 but some of there products have been updated (1000, and 3000) so I want to take the one with the newest timestamp out of the bunch.
So in the end I should get the values in fields data1, data2, data3, data4, and data5 for pid 1000, 2000, and 3000 but only for the record with the latest date of those products. Understand?